Bulk insert into table from csv

Question

Post reply

Bulk insert into table from csv

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

jaryszek Default port Points: 1425 More actions · Answer 1

thank you Jeff!

I opened the file through notepad and save it and utf8 encoding.
There is the problem still.

So my Data_Zmiany is date and here is a problem with encoding ?
I do not understand this.

Please find also the attachment with my file.

jaryszek Default port Points: 1425 More actions · Answer 2

Strange thing is that Length of my Data_Zmiany is only 3 (?)

What does it mean ?

Jacek

jaryszek Default port Points: 1425 More actions · Answer 3

Here is a solution for this but first option (removing header row) is not working for me:
Link

Maybe better solution will be create format file?

JAcek

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

I thought you solved it...

Hmm it is very strange - now i file was uploaded with success!

--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 Default port Points: 1425 More actions · Answer 5

Yestarday it was working and today with my new csv file generated from qlikview - there is a problem...:/

jaryszek Default port Points: 1425 More actions · Answer 6

Hi Guys,

could you please do it step by step as i I did in the described order?
I can handle with this and my boss is chasing me 😉

Thank you,
Best wishes,
Jacek

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

jaryszek - Wednesday, December 6, 2017 9:32 AM
Hi Guys,
could you please do it step by step as i I did in the described order?
I can handle with this and my boss is chasing me 😉
Thank you,
Best wishes,
Jacek

I believe this falls into the category of "Must Look Eye". 😉 In the code you provided further above, you create a table called "tbl_Slownik". Now... look at the view you provided code for and that you're BULK Inserting into. What table is it calling?

That's the only thing I changed in the code and everything worked just fine with the file you provided. The trouble is that, somewhere along the line, you destroyed the Unicode nature of the file and it's now just a normal text file and the special Unicode characters the file seems to contain will no longer come in properly.

--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 Default port Points: 1425 More actions · Answer 8

Hi Jeff. Thank you for help !

I believe this falls into the category of "Must Look Eye".

haha very funny 😎

Few things have been changed in my model. I will write exactly what i am doing in following steps:

1. I have 14 million records in csv file from QlikView.
Earlier i had a problem using visual studio to import them but now after adding null values i am creating my source table in database.

And now for this table i have to append new rows form csv.

Of course iwth bulk insert there is an error connected with Data_zmiany column,
In Data_Zmiany column there is datetime type and within my CSV there is only date in format "2017-03-01".

So i think that here is an error.
Here is Data_zmiany in MS SQL imported table with 14 million rows:

and date in my csv file is in format:
2017-03-01

I think that the best option here is to create staging table (temp table?) here ?

Please help,
Warm Regards,
Jacek

jaryszek Default port Points: 1425 More actions · Answer 9

I changed the date format from QlikView from "yyyy-mm-yy" to now() format which is "2017-12-07 10:38:14" and it is success!

But i want to find out about this tomorrow 🙂

I will let you know,
Jacek

jaryszek Default port Points: 1425 More actions · Answer 10

On more problem here.

I read that there is a problem with reading UTF-8 encoded files.
This is working (for now) but without polish signs...

If i changed my csv file to unicode all is workinf fine.
It is possible to do it via MS SQL Server?

Best Wishes,
Jacek

jaryszek Default port Points: 1425 More actions · Answer 11

Hi Guys,

it is me again...
Unfortunately is not working.

I have 2 applications in qlikview, second one is creating file which is not working (first one is working fine).
question is...why?!

In zip file i attached 2 files:
one with WORKING word - here is working with my bulk insert:

USE QlikView GO

BULK INSERT [VW_Slownik] FROM 'C:\Users\ljar01\Desktop\Dane_Slownik.csv' WITH ( FIRSTROW =2, DatafileType = 'widechar', FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' );

exec sp_help tbl_QlikView

What is the difference between these files?

Best Wishes,
Jacek

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

What error are you getting for the one that doesn't work?

--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 Default port Points: 1425 More actions · Answer 13

hi Guys,

thank you for helping me!

Problem was very simple! From QlikView imported data was in disorder!
Very simple...

Thank you !
Jacek

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

jaryszek - Tuesday, December 12, 2017 12:05 AM
hi Guys,
thank you for helping me!
Problem was very simple! From QlikView imported data was in disorder!
Very simple...
Thank you !
Jacek

Thanks for the feedback but what do you mean by "data was in disorder"?

--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 Default port Points: 1425 More actions · Answer 15

Columns in CSV were in different order then columns in my SQL tables...

Jacek