Support_38
asked on
Import csv for SQL Server
Good afternoon,
I would like your help again.
I have some files in csv format to be imported into SQL Server.
The files have the following format:
report_SLA08_20160601.csv
Where 01 would be the 06 month and 2016 year.
I have a file for each day of this, I need to import these files to the SQL SERVER automatically where each file would be a different table.
Could help me do this in powershell because the way I'm doing, it takes a long time.
Detail:
Each CSV file has a character | the end of the line, I have to take this character of each file to import does not cause error.
attached a file of the model.
Thank you.
REPORT_SLA08_20160601.csv
I would like your help again.
I have some files in csv format to be imported into SQL Server.
The files have the following format:
report_SLA08_20160601.csv
Where 01 would be the 06 month and 2016 year.
I have a file for each day of this, I need to import these files to the SQL SERVER automatically where each file would be a different table.
Could help me do this in powershell because the way I'm doing, it takes a long time.
Detail:
Each CSV file has a character | the end of the line, I have to take this character of each file to import does not cause error.
attached a file of the model.
Thank you.
REPORT_SLA08_20160601.csv
ASKER
Hello,
The file is not being created in tabular format between the columns.
an attached print
select.png
The file is not being created in tabular format between the columns.
an attached print
select.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I run the script, I am getting the below error:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
I used the sample from the question in 2 separate files and both were imported with no issues. Please post here the content of the batch file and the sample you tried to import. Is it possible that some columns are wider that the size in the table?
report_SLA08_20160601.csv
report_SLA08_20160601.csv
ASKER
Hello
I'm using the same example you, but for me the error has already informed.
Below the contents of my .bat file
@echo off
for /r E:\temp\EE %%f in (*.csv) do (
bcp [NEW_SLA].[dbo].[REPORT_SL
)
I'm using the same example you, but for me the error has already informed.
Below the contents of my .bat file
@echo off
for /r E:\temp\EE %%f in (*.csv) do (
bcp [NEW_SLA].[dbo].[REPORT_SL
)
What about the [NEW_SLA].[dbo].[REPORT_SL
If yes then it has to work. Use the file I attached. Just download it.
If yes then it has to work. Use the file I attached. Just download it.
ASKER
I created the Report Sla 08 table just with the script you sent.
I used the same file that I have attached, I do not know if it could be something related to my SQL client, but I think not.
I used the same file that I have attached, I do not know if it could be something related to my SQL client, but I think not.
Remove -U from that line in the batch file. I don't use that. I just noticed it now.
ASKER
Unfortunately had the same error, try to run on another server. :-(
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]String data, right truncation
23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (23000.00 rows per sec.)
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]String data, right truncation
23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (23000.00 rows per sec.)
ASKER
Now I work, I created a table with some varchar fields with higher values.
How do I leave only the first line with field name?
attached the result
Capturar.PNG
How do I leave only the first line with field name?
attached the result
Capturar.PNG
ASKER
I also had to remove the option -r0x7c0d0a
only then stopped display error
only then stopped display error
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I also had to remove the option -r0x7c0d0aIf you remove that you will have the pipe, |, attached to the last date column. Like I said it worked for me and I am not sure why doesn't work for you.
only then stopped display error
To skip first row in BCP you can try -F1 switch, to ignore the first row. Again, it worked for me as well, the header row is not imported without using -F1 or any other switch than the ones I posted.
ASKER
@Pawan
Sorry about the delay in response, your solution worked perfectly so, now just need to tailor it to import all the files at once.
Sorry about the delay in response, your solution worked perfectly so, now just need to tailor it to import all the files at once.
You just run that batch file. It will import all the csv files from the folder one by one in one run to the same table.
Open in new window