Safe way to BULK INSERT a CSV file in SQL SERVERBest way to get identity of inserted row?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?Check if table exists in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?LEFT JOIN vs. LEFT OUTER JOIN in SQL ServerInserting multiple rows in a single SQL query?How do I UPDATE from a SELECT in SQL Server?how to import file csv without using bulk insert query?
Insert str into larger str in the most pythonic way
Should I leave the first authourship of our paper to the student who did the project whereas I solved it?
Can a magnet rip protons from a nucleus?
Why was "leaping into the river" a valid trial outcome to prove one's innocence?
Are Democrats more likely to believe Astrology is a science?
How do email clients "send later" without storing a password?
Relevance of the Resurrection
How do I politely hint customers to leave my store, without pretending to need leave store myself?
A Little Riddle
Can I cast Sunbeam if both my hands are busy?
Is the union of a chain of elementary embeddings elementary?
Do ibuprofen or paracetamol cause hearing loss?
What is the purpose of libraries like Pyomo and Google OR tools?
How can I protect myself in case of a human attack like the murders of the hikers Jespersen and Ueland in Morocco?
Evidence that matrix multiplication cannot be done in O(n^2 poly(log(n))) time
Random point on a sphere
Why did they ever make smaller than full-frame sensors?
Can I use ratchet straps to lift a dolly into a truck bed?
Have there been any countries that voted themselves out of existence?
How can I locate a missing person abroad?
Why do sellers care about down payments?
Might have gotten a coworker sick, should I address this?
Is Salesforce Classic being deprecated?
Are programming languages necessary/useful for operations research practitioner?
Safe way to BULK INSERT a CSV file in SQL SERVER
Best way to get identity of inserted row?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?Check if table exists in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?LEFT JOIN vs. LEFT OUTER JOIN in SQL ServerInserting multiple rows in a single SQL query?How do I UPDATE from a SELECT in SQL Server?how to import file csv without using bulk insert query?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
AS many before, I've fallen into the BULK INSERT hell with SQL Server and CSV files. I've tried to follow basically all the previous anwers but with no luck.
e.g.
'BULK INSERT ' + @Table
+ ' FROM ''' + @Path + @Table + '.csv'' WITH (
FIRSTROW = 2, FIELDTERMINATOR = '';'', ROWTERMINATOR = ''rn'', TABLOCK ) '
Good thing is I can create the CSV files from scratch (I'm on windows, but i'd need them to work in Linux as well.)
Is there a safe methodcode combination to create a CSV file on Windows that will be read by BULK INSERT in both Linux and Windows?
Thank you in advance.
sql-server bulkinsert asked Mar 28 at 8:57
Fed CFed C
86 bronze badges
add a comment |
AS many before, I've fallen into the BULK INSERT hell with SQL Server and CSV files. I've tried to follow basically all the previous anwers but with no luck.
e.g.
'BULK INSERT ' + @Table
+ ' FROM ''' + @Path + @Table + '.csv'' WITH (
FIRSTROW = 2, FIELDTERMINATOR = '';'', ROWTERMINATOR = ''rn'', TABLOCK ) '
Good thing is I can create the CSV files from scratch (I'm on windows, but i'd need them to work in Linux as well.)
Is there a safe methodcode combination to create a CSV file on Windows that will be read by BULK INSERT in both Linux and Windows?
Thank you in advance.
sql-server bulkinsert asked Mar 28 at 8:57
Fed CFed C
86 bronze badges
I would change the ROWTERMINATOR from'' r n''to''0x0a''
– Killer Queen
Mar 28 at 10:38
Read what you wrote. If you knew nothing about this code, your data, your schema, and the process used, would you be able to provide any help? What does "no luck" mean? Is the problem with your construction of dynamic sql? Is it with the execution of your dynamic sql? Maybe permissions to the randomly named file you attempt to insert? Maybe you should use a different approach - not one based on executing this from the server instance but from a client machine?
– SMor
Mar 28 at 12:50
add a comment |
AS many before, I've fallen into the BULK INSERT hell with SQL Server and CSV files. I've tried to follow basically all the previous anwers but with no luck.
e.g.
'BULK INSERT ' + @Table
+ ' FROM ''' + @Path + @Table + '.csv'' WITH (
FIRSTROW = 2, FIELDTERMINATOR = '';'', ROWTERMINATOR = ''rn'', TABLOCK ) '
Good thing is I can create the CSV files from scratch (I'm on windows, but i'd need them to work in Linux as well.)
Is there a safe methodcode combination to create a CSV file on Windows that will be read by BULK INSERT in both Linux and Windows?
Thank you in advance.
sql-server bulkinsert asked Mar 28 at 8:57
Fed CFed C
86 bronze badges
AS many before, I've fallen into the BULK INSERT hell with SQL Server and CSV files. I've tried to follow basically all the previous anwers but with no luck.
e.g.
'BULK INSERT ' + @Table
+ ' FROM ''' + @Path + @Table + '.csv'' WITH (
FIRSTROW = 2, FIELDTERMINATOR = '';'', ROWTERMINATOR = ''rn'', TABLOCK ) '
Good thing is I can create the CSV files from scratch (I'm on windows, but i'd need them to work in Linux as well.)
Is there a safe methodcode combination to create a CSV file on Windows that will be read by BULK INSERT in both Linux and Windows?
Thank you in advance.
sql-server bulkinsert sql-server bulkinsert asked Mar 28 at 8:57
Fed CFed C
86 bronze badges
asked Mar 28 at 8:57
Fed CFed C
86 bronze badges
asked Mar 28 at 8:57
Fed CFed C
86 bronze badges
asked Mar 28 at 8:57
Fed CFed C
86 bronze badges
asked Mar 28 at 8:57
Fed CFed C
86 bronze badges
86 bronze badges
I would change the ROWTERMINATOR from'' r n''to''0x0a''
– Killer Queen
Mar 28 at 10:38
Read what you wrote. If you knew nothing about this code, your data, your schema, and the process used, would you be able to provide any help? What does "no luck" mean? Is the problem with your construction of dynamic sql? Is it with the execution of your dynamic sql? Maybe permissions to the randomly named file you attempt to insert? Maybe you should use a different approach - not one based on executing this from the server instance but from a client machine?
– SMor
Mar 28 at 12:50
add a comment |
I would change the ROWTERMINATOR from'' r n''to''0x0a''
– Killer Queen
Mar 28 at 10:38
Read what you wrote. If you knew nothing about this code, your data, your schema, and the process used, would you be able to provide any help? What does "no luck" mean? Is the problem with your construction of dynamic sql? Is it with the execution of your dynamic sql? Maybe permissions to the randomly named file you attempt to insert? Maybe you should use a different approach - not one based on executing this from the server instance but from a client machine?
– SMor
Mar 28 at 12:50
I would change the ROWTERMINATOR from
'' r n'' to ''0x0a''– Killer Queen
Mar 28 at 10:38
I would change the ROWTERMINATOR from
'' r n'' to ''0x0a''– Killer Queen
Mar 28 at 10:38
Read what you wrote. If you knew nothing about this code, your data, your schema, and the process used, would you be able to provide any help? What does "no luck" mean? Is the problem with your construction of dynamic sql? Is it with the execution of your dynamic sql? Maybe permissions to the randomly named file you attempt to insert? Maybe you should use a different approach - not one based on executing this from the server instance but from a client machine?
– SMor
Mar 28 at 12:50
Read what you wrote. If you knew nothing about this code, your data, your schema, and the process used, would you be able to provide any help? What does "no luck" mean? Is the problem with your construction of dynamic sql? Is it with the execution of your dynamic sql? Maybe permissions to the randomly named file you attempt to insert? Maybe you should use a different approach - not one based on executing this from the server instance but from a client machine?
– SMor
Mar 28 at 12:50
add a comment |
1 Answer
1
active
oldest
votes
In the end, I've been able to solve it by using ANSI encoding and by substituting all the ; with ,. The code I used is the following: 'BULK INSERT ' + @Table + ' FROM ''' + @Path + @Table + '.csv'' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''n'')'
answered Mar 29 at 11:33
Fed CFed C
86 bronze badges
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55393539%2fsafe-way-to-bulk-insert-a-csv-file-in-sql-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
In the end, I've been able to solve it by using ANSI encoding and by substituting all the ; with ,. The code I used is the following: 'BULK INSERT ' + @Table + ' FROM ''' + @Path + @Table + '.csv'' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''n'')'
answered Mar 29 at 11:33
Fed CFed C
86 bronze badges
add a comment |
In the end, I've been able to solve it by using ANSI encoding and by substituting all the ; with ,. The code I used is the following: 'BULK INSERT ' + @Table + ' FROM ''' + @Path + @Table + '.csv'' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''n'')'
answered Mar 29 at 11:33
Fed CFed C
86 bronze badges
add a comment |
In the end, I've been able to solve it by using ANSI encoding and by substituting all the ; with ,. The code I used is the following: 'BULK INSERT ' + @Table + ' FROM ''' + @Path + @Table + '.csv'' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''n'')'
answered Mar 29 at 11:33
Fed CFed C
86 bronze badges
In the end, I've been able to solve it by using ANSI encoding and by substituting all the ; with ,. The code I used is the following: 'BULK INSERT ' + @Table + ' FROM ''' + @Path + @Table + '.csv'' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''n'')'
answered Mar 29 at 11:33
Fed CFed C
86 bronze badges
answered Mar 29 at 11:33
Fed CFed C
86 bronze badges
answered Mar 29 at 11:33
Fed CFed C
86 bronze badges
answered Mar 29 at 11:33
Fed CFed C
86 bronze badges
86 bronze badges
add a comment |
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55393539%2fsafe-way-to-bulk-insert-a-csv-file-in-sql-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I would change the ROWTERMINATOR from
'' r n''to''0x0a''– Killer Queen
Mar 28 at 10:38
Read what you wrote. If you knew nothing about this code, your data, your schema, and the process used, would you be able to provide any help? What does "no luck" mean? Is the problem with your construction of dynamic sql? Is it with the execution of your dynamic sql? Maybe permissions to the randomly named file you attempt to insert? Maybe you should use a different approach - not one based on executing this from the server instance but from a client machine?
– SMor
Mar 28 at 12:50