October 29, 2007 at 8:58 am
Hi,
I am currently importing spreadsheets into SQL Server 2000 using A DTS package, all the spreadsheets import fine until I had to import two spreadsheets which are 26 MB each.
Obviously they are taking much longer due to their size and I was wandering if anyone new any quicker ways of importing spreadsheets of such a large size?
any advice or pointers in the right direction would be much appreciated.
Thanks Mark
October 29, 2007 at 3:47 pm
Have u tried Bulk insert. Thats what we use for the big files.
October 29, 2007 at 5:52 pm
I don't know what type of data you are importing, DateTime, Int, BigInt,varchar(xxxx) but when SQL opens an Office file, it requires some dll to do this. Try Saving the spreadsheet as a flat text file each (Assuming there is only one table) then import via SSIS. It Should double your performance.
October 30, 2007 at 3:02 am
Hi,
I've imported the file as a text file (as it's in .csv format) and created a DTS package, and it now works a treat. Thank you Mayank and Warren for your comments.
Cheers Mark 😀
October 31, 2007 at 8:20 am
Can you please provide the step by step explanation on SSIS implementation, if possible provide few screen shots too.
By the way what method were you using earlier, can you detail about that as well.
October 31, 2007 at 8:53 am
Mark,
I use SSIS to import Spreadsheets into tables and to export tables to spreadsheets, with quite a bit of success, even with large files.
The basic how to would be:
Open SQL Server Business Intelligence Development Studio
Create a new Integration Services Project
Add a Data Flow Task in the Control Flow tab
Add Excel Source to the Data Flow tab
-Configure the Excel Source
Add a Data Conversion Task
-Configure the Data Conversion Task
Add a OLE DB Destination or SQL Server Destination depending on where the package will execute from
(If it'll run on the target server you can use SQL Server Destination, if it'll run from somewhere else, use the OLE DB Destination.)
-Configure the Destination
Then I just click on the Control Flow tab
And Right Click the Data Flow Task and choose execute to test.
If it's taking too long for you, you might xcopy the spreadsheet over to the server and import it from there, that would be less network I/O in your way.
October 31, 2007 at 9:38 am
Keep in mind when importing from an Excel spreadsheet, for Excel the Maximum rows in Excel for a sheet is 65,535. Not too big when compared to SQL. We regularly import 27 million records daily on one DB from csv files and other flat sources that come from Unix based apps.
November 5, 2007 at 3:33 am
Hi,
I'm now using SQL Server 2005 and i've followed your instructions which have been a great help. But i need to import excel files and also .csv files. Is there any way of importing .csv files?
Many thanks for your help
Mark
November 5, 2007 at 7:18 am
To import a .csv file, you use the same steps I outlined above, but you use a Flat File source instead of an Excel source.
Good Luck!
November 5, 2007 at 7:24 am
Thanks for your help Jim, I had been using SQL Server 2000 before and now I have to also do it on SQL Server 2005. It was just slightly different using Visual Studio, but I've got it working fine now.
Thanks again for your help.
Cheers Mark 🙂
November 5, 2007 at 7:25 am
Anytime, glad I could help! Have fun moving data!
May 7, 2008 at 11:08 am
please help me out:
I am trying to write a simple SSIS package that will take in a directory name, and using a for each loop process through and import data from each file whether it be in csv, txt, or raw formats, then clean and purge data if necessary to look like the following format:
Firstname, Lastname, MiddleName, Address, City, State Zip
then import that data into 2 (SQL SERVER 2005) related tables
Ex.
Table 1
Person (PersonID, FirstName, LastName, MiddleName)
Where personID is an identity
Table 2
PersonLocation(PersonLocationID, PersonID, Address, City, State, Zip)
The parameters for this package would be a directory name
I am a complete beginner and love "being talked down to" so I would appreciate any and all help, or direction you could give.
May 7, 2008 at 12:12 pm
Tech_Newbie:
The place to start is with the files themselves. The main question is, do they have anything resembling a standardized format? Like, all the text files have the same format as each other, and the csv files are different from the txt files, but are the same as each other. Is that true? (If so, it makes this relatively easy. If not, it becomes more difficult.)
The next thing to do is take a look at the ForEachNext loop in SSIS. It can go through all the files in a folder and perform actions on each of them.
Start with those two steps, and expect to spend some time digging through Books Online and these forums.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 8, 2008 at 9:14 am
thanks for responding,
No its "to each his own" for each file - some text files are tab delimited, some are csv, some are | delimited. the same is true for the flat files. Some text files contain extra information, and some contain only the information needed. Can you point me in a direction for a good C# and SSIS (almost "for dummies" type) resource that can get me started on accomplishing this?
May 8, 2008 at 1:48 pm
I don't know one I can recommend. I'd just fire up Google and start looking.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply