Bulk insert into table from csv
-
Hi,
i have table in CSV:
As you can see there are a lot of columns.My source table data types fields are:
so now i want to insert from my csv file into my source table.
I have tried with:
USE QlikView
GOBULK
INSERT dbo.tbl_SLownik_SourceMS
FROM 'C:\Users\ljar01\Desktop\Dane_Slownik.csv'
WITH
(
FIRSTROW =2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)but there are errors connected with conversion data types...:
How can i write it properly ?
And i want to create automation for the future purpose using MS SQL Server Express Edition.So CSV file will be every day updated with the fresh data.
And next the batch file will be running SQL Command where data from CSV file will be loaded into my source table (insert into statement).Please help Guys,
Best wishes,
Jacek Antek -
Quick thought, you are trying to insert the unique id string into the id column (int), you will have to specify the target columns to avoid this.
😎 -
Looks like the identity specification hasn't been set so its trying to insert UniqueID into ID.
- I'll also ask, if it's a "CSV" (the "C" stands for "Comma"), is your delimiter actually correct?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Guys,
thank you for helping !
MY CSV is not looking like standard CSV.
Is looking like Excel with columns.Ok, how can i specify syntax in order to insert Unique ID not into ID autonumber column?
Best Wishes,
Jacek - jaryszek - Monday, December 4, 2017 3:52 AM
Has ID been set as an identity? Your sp_help doesn't show the constraint.
Then you will need to specify the column names in a format file to insert into
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sqlBULK
INSERT dbo.tbl_SLownik_SourceMS
FROM 'C:\Users\ljar01\Desktop\Dane_Slownik.csv'
WITH
(
FORMATFILE ('C:\Users\ljar01\Desktop\Dane.Slownik.xml'),
FIRSTROW =2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)To iterate Jeff's post, I would also confirm the field delimiter, CSV files open in EXCEL, open it in NOTEPAD or your best text editor and check that the column delimiter is actually ; and not ,
-
Hi,
thank you Anthony.
I think that your code is working but without using FORMATFILE:so here is a problem with ID field which is set as Identity. (btw how to check what field is Identity using command? )
Yes, delimeter is correct whrn i am opening my CSV file using notepad:
So how to insert into my table only choosen rows.
So i want to create bulk insert or maybe the better approach is to use standard insert into syntax?Best Wishes,
Jacek -
Yes you will need to create the format file to detail what format to import. The link I mentioned gives examples of when to use a format file, and there is plenty on the web on creating a BCP format file that it should be fairly trivial to complete.
-
@jaryszek ,
You need to generate and post the full CREATE TABLE statement for your target table for us to help further. I also recommend that you do NOT import directly to the final table. Rather, you should import to a "staging" table so that you can validate data prior to pushing the data to the final table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Guys,
thank you for helping me,
my code is:USE QlikView
GOCREATE TABLE tbl_Slownik
(
ID INT IDENTITY(1,1),
UniqueID NVARCHAR(50),
Data_Zmiany Date,
PESEL float(53) NULL,
Person_ID NVARCHAR(50),
Nazwisko NVARCHAR(50),
Imie NVARCHAR(50),
Department NVARCHAR(50),
Company_NIP NVARCHAR(50),
Company_name NVARCHAR(50),
Company_shortcut NVARCHAR(50),
Systems NVARCHAR(50),
)SELECT
ID,
UniqueID,
Data_Zmiany,
PESEL,
Person_ID,
Nazwisko,
Imie,
Department,
Company_name,
Company_shortcut,
Systems,
CAST(Company_NIP as float) as Company_NIP
INTO tbl_SLownik_SourceMS
FROM QlikView.dbo.tbl_SlownikSo i have 2 tables.
And now i have used:
CREATE VIEW [dbo].[VW_Slownik]
AS
SELECT UniqueID,
Data_Zmiany,
PESEL,
Person_ID,
Nazwisko,
Imie,
Department,
Company_NIP,
Company_name,
Company_shortcut,
Systems
FROM dbo.tbl_SLownik_SourceMSSELECT * FROM VW_Slownik
BULK
INSERT VW_Slownik
FROM 'C:\Users\ljar01\Desktop\Dane_Slownik.csv'
WITH
(
FIRSTROW =2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
);Bul insert is throwing the error:
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (Data_Zmiany).thank you for helping me,
Jacek - jaryszek - Monday, December 4, 2017 7:32 AM
The next thing we need is the record layout for the file that you're trying to import. If there is no PII or other sensitive information in the file, you could zip it and attach it to your post. If there is ANYTHING sensitive or proprietary, then attach just the header row.
In any case, we need to know what the mapping between what the file is and the intended table target by field name in the file and column name in the table, especially if fields in the file are to be ignored or columns in a table that are not in the file.
p.s. Don't post any of that as a graphic, please. Post it as text that we can copy from your post.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Guys,
in attachment there is my flat file from where I am trying to upload data into MS SQL Server.
Please help with creating Query for it..
Thank you
Jacek -
Hmm it is very strange - now i file was uploaded with success!
I will test the file tomorrow also and i will let you know if it is working...
Best Wishes,
Jacek - jaryszek - Tuesday, December 5, 2017 5:08 AM
Ah... I see the problem you're having. The file has UTF-8 encoding. This has been a very common file type for a very long time and it's amazing that SQL Server and BCP don't actually support it until (IIRC), SQL Server 2016. I've always been able to get the data provider to send just plain ol' ASCII (DOS) files in the past and so I've never looked for a way to work with UTF-8 files in SQL Server.
I have to get to work and so haven't had much time to play with the file (there's always a way to trick something) but Excel lists the file as "Unicode Text" if you try to save it. You might want to try the DATAFILETYPE = 'widechar' setting for your BULK INSERT and see what happens. Don't forget that the first row will have an extra 4 character prefix on the first column label.
If you need to load the file immediately regardless of whether or not you can automate it, then do load it in Excel and save it as a UNICODE TEXT file and when you import, use Tab as the delimiter. Again, I believe you'll have to use the DATAFILETYPE = 'widechar' setting for your BULK INSERT if you save it as a UNICODE TEXT file.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - jaryszek - Tuesday, December 5, 2017 6:00 AM
Perhaps you accidently saved it from Excel?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply