SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stuck on trying to do a large import


stuck on trying to do a large import

Author
Message
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 214
Hi All


I am trying to do a large import with the below code for many thousands of rows from an excel spreadsheet.

the table is made up as follows

col1, col2, col3 col4, col5

sun microsystems inc, test,test,test,test
sun microsystems, inc, test, test, test, test
adobe inc, test,test,test,test
microsoft,test,test,test,test
microsoft",test,test,test,test
"adobe, inc",test,test,,test

I am having problems for example with rows 2, 5 and 6

all rows are inserting but for instance row 2 is finding the comma after sun microsystems, then putting inc in the next column along
same for row 6 with adobe ones
row 5 is still inserting which is similar two the other rows but i think it is doing the same because of the the double quotes
also the last row is showing no value so is there anyway to put some wording into the row if it is empty say something like 'Empty Value'

here is the import I am using

BULK
INSERT The_Big_Kahuna
FROM 'c:\users\alynch\Desktop\The_Big_Kahuna.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
SELECT *
FROM dbo.The_Big_Kahuna
GO


thanks
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10295 Visits: 13559
You'll need to standardize the input source before even trying the BULK INSERT.
SQL Server is a great tool but it cannot "guess" what the final result should look like from your personal perspective.
You need to provide a field terminator that is not part of the values you're trying to insert.
Maybe you could use a pipe separator or something like this.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16599 Visits: 10063
From your BULK INSERT statement, you're not actually importing from an Excel file, but from a CSV file.

This is an important distinction because it's been my experience that you have a much better shot at getting your input file standardized when using CSV. Excel interprets lots of values such as leading zeros, dates, etc. that make it virtually impossible to get things standardized. If this is going to be a repeated process that runs on a server, Excel can also have VBA code associated with it and you probably don't want to take a chance by running it on your server, especially if it comes from a third party.

I've found that for repeated loads from consistent data files, using BULK INSERT with a format file is very reliable. It's a bit of work to set up the XML format file, but it is ultra-fast and very consistent. http://msdn.microsoft.com/en-us/library/ms178129.aspx A point to consider is that both the file with the source data and the format file need to be on the server itself.

HTH


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search