Solved

Import csv for SQL Server

Posted on 2016-09-29
17
52 Views
Last Modified: 2016-09-30
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
0
Comment
Question by:Support_38
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
17 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41821915
Here it is..

SELECT * INTO #pawan 
FROM OPENROWSET (BULK 'E:\pawan1.csv', SINGLE_CLOB) MyFile 

SELECT * FROM #pawan

Open in new window

0
 

Author Comment

by:Support_38
ID: 41821976
Hello,

The file is not being created in tabular format between the columns.

an attached print
select.png
0
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 333 total points
ID: 41822055
Here it is .. Enjoy !!

Remove last | from the CSV file. It is a bug.

CREATE TABLE testCSV
(
	 Name NVARCHAR(1000)
	,Disponibilidade NVARCHAR(1000)
	,Data NVARCHAR(1000)
	,Report NVARCHAR(1000)
	,Country NVARCHAR(1000)
	,Last_refresh NVARCHAR(1000)
)
GO

BULK
INSERT testCSV
FROM 'E:\Pawan1.CSV'
WITH
(
FirstRow = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '|'
)
GO

SELECT * FROM testCSV

Open in new window

Pawan1.csv
1
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 167 total points
ID: 41822087
1. Create this table in your database:
CREATE TABLE [dbo].[report_SLA08](
	[Name] [varchar](250) NULL,
	[Disponibilidade] [varchar](10) NULL,
	[Data] [varchar](50) NULL,
	[Report] [varchar](250) NULL,
	[Country] [varchar](250) NULL,
	[Last_refresh] [varchar](50) NULL
) ON [PRIMARY]

GO

Open in new window

2. Create a working folder on your computer that has BCP installed(any computer with SQL server on it) and copy all the CSV files into it. For example purposes I named it C:\TEMP\EE :

3. In the same folder create a batch file named _import_report_SLA08.bat with the following content:
@echo off
for /r C:\TEMP\EE %%f in (*.csv) do (
bcp [YourDB].[dbo].[report_SLA08] in %%f -T -c -t; -r0x7c0d0a -SDEVSQL03 > %%f.output
)

Open in new window

Of course you will replace YourDB with the real database name.

3. Open the folder and execute the batch file by double clicking on it.


This script will look in the folder and it will search all the .CSV files and loop through them and import them one by one into the table created at step 1. For every CSV file there will be created an output file with the same name plus the .output extension. Those files will keep the import results.

EDIT:

I forgot to mention that the BCP command uses as row separator the {|}{CR}{LF} sequence so you don't have to worry about the | (pipe) at the end of the rows.

Another thing is that the Disponibilidade column has comma as decimal separator instead of dot. For this reason the column is of varchar type and you will have to replace the comma with dot in the table after the import:
update [YourDB].[dbo].[report_SLA08] set [Disponibilidade] =replace([Disponibilidade] ,',','.')

Open in new window

Only then you will be able to convert it to number, I think it is a pecentage.
0
 

Author Comment

by:Support_38
ID: 41822314
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
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41822326
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?
csv_import.JPGreport_SLA08_20160601.csv
0
 

Author Comment

by:Support_38
ID: 41822517
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_SLA08] in %%f -U -T -c -t; -r0x7c0d0a -SVBR001001-015\AUDITORIA > %%f.output
)
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41822528
What about the [NEW_SLA].[dbo].[REPORT_SLA08] table? Does it have the same structure like the one I posted?

If yes then it has to work. Use the file I attached. Just download it.
0
 

Author Comment

by:Support_38
ID: 41822553
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.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41822573
Remove -U from that line in the batch file. I don't use that. I just noticed it now.
0
 

Author Comment

by:Support_38
ID: 41822661
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.)
0
 

Author Comment

by:Support_38
ID: 41822679
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
0
 

Author Comment

by:Support_38
ID: 41822708
I also had to remove the option -r0x7c0d0a
only then stopped display error
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 333 total points
ID: 41822822
@Alex - Have you tried the code I have given you ? It was really easy to understand.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41823378
I also had to remove the option -r0x7c0d0a
only then stopped display error
If 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.

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.
0
 

Author Comment

by:Support_38
ID: 41823986
@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.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41824161
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question