Unable to import all rows from .csv

Question

Post reply

Unable to import all rows from .csv

Kevin Drysdale

SSC-Addicted

Points: 453
More actions
December 5, 2011 at 1:13 pm

#253022

I'm trying to import data from a .csv file into a table and it consistently imports 2033 less rows than the total in the file. I've manipulated the number of records in the file, and it's consistently 2033 less than the total. The import is successful, just not all the rows are imported.
I did test importing a different set of data into a different table in the same database, and it successfully imported all the records. So it appears to be an issue with this particular table and not the database.
Has anyone seen anything like this?
Kevin

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

how are you importing? SSIS? BULK INSERT? are you importing to a staging table first?

I'm sure there's an explanation, we just gotta dig a little bit.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kevin Drysdale SSC-Addicted Points: 453 More actions · Answer 2

Using Import/Export Wizard. This is a test database. Taking it straight to the table. I did drop and recreate the table. Same thing. I can try creating a staging table.

Jared SSC Guru Points: 61793 More actions · Answer 3

Is it possible you have some data problems in the csv? For example, in the column mapping of the wizard, are there more columns than there should be?

Thanks,

Jared

Jared
CE - Microsoft

Kevin Drysdale SSC-Addicted Points: 453 More actions · Answer 4

Pretty sure I checked thoroughly and it looks clean. The last column did contain data with commas, and the delimiter was a comma. I requested they send a data file using pipes, which cleaned that up. But still same problem.

I will double check that file again. There has to be something I overlooked.

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

Kevin Drysdale (12/5/2011)
Pretty sure I checked thoroughly and it looks clean. The last column did contain data with commas, and the delimiter was a comma. I requested they send a data file using pipes, which cleaned that up. But still same problem.
I will double check that file again. There has to be something I overlooked.

Might I suggest that you look for values that are not supported by MS, such as:

1. Start of heading a hx 1 Oct 1

2. Start of text 2 2

3. End of text 4 4

For a comprehensive list of these types of characters go to:

http://www.LookupTables.com

And at that site in the main task bar click on ASCII (left most item on the task bar).

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]

subbu1 Hall of Fame Points: 3221 More actions · Answer 6

use ssis package for loading .csv file that time you find out what is excact issue in .csv file.

Divine Flame SSCoach Points: 15943 More actions · Answer 7

Kevin Drysdale (12/5/2011)
I'm trying to import data from a .csv file into a table and it consistently imports 2033 less rows than the total in the file. I've manipulated the number of records in the file, and it's consistently 2033 less than the total. The import is successful, just not all the rows are imported.
I did test importing a different set of data into a different table in the same database, and it successfully imported all the records. So it appears to be an issue with this particular table and not the database.
Has anyone seen anything like this?
Kevin

Have you tried using BCP utility ? Are you getting the same result with that also ?

Sujeet Singh

texpic SSCertifiable Points: 5885 More actions · Answer 8

texpic

SSCertifiable

Points: 5885

December 5, 2011 at 10:43 pm

#1417114

Why not delete the table and recreate it.

Divine Flame SSCoach Points: 15943 More actions · Answer 9

texpic (12/5/2011)
Why not delete the table and recreate it.

Using Import/Export Wizard. This is a test database. Taking it straight to the table. I did drop and recreate the table. Same thing. I can try creating a staging table.

He has already tried that.

Sujeet Singh

tommey152 Right there with Babe Points: 772 More actions · Answer 10

Kevin Drysdale (12/5/2011)
Using Import/Export Wizard. This is a test database. Taking it straight to the table. I did drop and recreate the table. Same thing. I can try creating a staging table.

If you have option go for SSIS...By following steps:

Open BIDS

1. Drag Data Flow Task on the Control Flow tab.

2. Double click on the Data Flow Task.

3. Drag Flat File Source on the Data Flow tab.

4. Double click on it.

5. Click on New button and add connection to .csv file.

6. Click on Columns list box item and select columns from the table.

7. Click on OK button.

8. Drag OLE DB Destination on the Data Flow tab.

9. Drag green arrow from it on the OLE DB Destination component.

10. Double click on it.

11. Click on new button and select destination Table to create.

12. Click on mappings list box item and select column mappings between .csv columns file and database columns.

13. Click on Control Flow tab.

14. press F5 and check the new table is crated are not....

_______________________________________________________________

Need help? Help us help you.