Import CSV File to SQL 2000

Question

Import CSV File to SQL 2000

diboy79

Old Hand

Points: 308
More actions
September 13, 2011 at 9:32 pm

#98394

Dear master,
I do not know why I can not import csv file into sql 2000 using DTS Import/Export Wizard, some error message that appears is:
1. could not find the selected row delimiter within the first 8 KB of Data
2. too many column found in the current row;non-white space characters were found after the last defined column's data
3. error calling GetNextRows
4. invalid delimited data;
thanks for your help...

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Many who might want to assist you do NOT like openning attachements .. possible bad software within.. so can you cut and paste a samplel into the main page of the forum ... say about 8 or 10 lines

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

diboy79 Old Hand Points: 308 More actions · Answer 2

thanks ron for you comment, but the file size is just 907KB and it is CSV file. (not virus file, just txt/flat file)

don't worry about this file, and thanks for every one that want to help me..

please...

crazy4sql SSCoach Points: 19590 More actions · Answer 3

try linked server with your excel file. After then you can query your excel linked server like database objects. Store the record from excel to temp table and then do import/export from one table to other.

----------
Ashish

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

diboy79 (9/13/2011)
1. could not find the selected row delimiter within the first 8 KB of Data
2. too many column found in the current row;non-white space characters were found after the last defined column's data
3. error calling GetNextRows
4. invalid delimited data;

Errors 2, 3, and 4 are caused by error 1. What delimiter ARE you trying to use for a row delimiter? I can tell you that CrLf won't do it in this case.

--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)

diboy79 Old Hand Points: 308 More actions · Answer 5

Jeff Moden (9/13/2011)Errors 2, 3, and 4 are caused by error 1. What delimiter ARE you trying to use for a row delimiter? I can tell you that CrLf won't do it in this case.

row delimiter use comma, and text qualifier with double quote. please, download that sample file for detail. i don't know exactly what structure csv file is...

🙂

diboy79 Old Hand Points: 308 More actions · Answer 6

diboy79

Old Hand

Points: 308

September 14, 2011 at 12:55 am

#1381074

this is screenshot for error:

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

diboy79 (9/14/2011)
Jeff Moden (9/13/2011)Errors 2, 3, and 4 are caused by error 1. What delimiter ARE you trying to use for a row delimiter? I can tell you that CrLf won't do it in this case.
row delimiter use comma, and text qualifier with double quote. please, download that sample file for detail. i don't know exactly what structure csv file is...
🙂

I did... that's why I wanted you to tell me what you were using for a row delimiter so I could tell if they match or not. 😉

--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)

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

diboy79 (9/14/2011)
this is screenshot for error:

That doesn't match any of the previous errors you listed. The error above is what you might get if part of the file got chopped off... like the file you posted.

Also, I can't read what the headings are in the file but the word "Bank" shows up a lot. It looks like this file could contain a fair bit of personal information. If that's true, please edit your post and delete the file from this site.

--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)

diboy79 Old Hand Points: 308 More actions · Answer 9

Jeff Moden (9/14/2011)
That doesn't match any of the previous errors you listed. The error above is what you might get if part of the file got chopped off... like the file you posted.
Also, I can't read what the headings are in the file but the word "Bank" shows up a lot. It looks like this file could contain a fair bit of personal information. If that's true, please edit your post and delete the file from this site.

The Heading is from "ID" to "KETERANGAN", and as your advice I had been deleted this file.

i don't know row delimiter is used?

diboy79 Old Hand Points: 308 More actions · Answer 10

I was trying to do not use DTS wizard, and use bulk insert lg attachment bellow, but it is not solve anything...:-D

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

I believe I can make BULK INSERT work for this but... since you originally posted a file that ended in a partial line, you should first check with the provider of the data and find out where the rest of the file went. Once you have a whole file, we can work on the problem of importing it.

--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)

diboy79 Old Hand Points: 308 More actions · Answer 12

thanks all, the csv file that I uploaded yesterday is full file. the problem is after the column name, one row with another row are separated by a comma, giving rise to confusion to distinguish between columns.

🙂