Import csv file into SQL 2005

Wildone63
Wildone63 used Ask the Experts™
on
I am trying to setup an automated routine that will import a csv file into SQL 2005 on a daily basis.

I get this csv file from our fedex shipping application.

I have tried several ways to import this file but I keep getting errors.

Basically I am chosing a flat file for the source, I go through and map the fields etc... then I keep getting errors on the conversion etc... My though would be to treat all of the fields as text except the two date fields.

This should not be this hard but I have spent the last 4 hours trying to make this work...

I also need this routine to pickup the file from lastnights run and import it each day.... the file is named different for each day...fedex1-10212009.csv is yesterdays fedex1-10222009.csv will be tomorrows

Here is a sample from the csv file I get each night...

118718/DA,  25403=PO,963499661520,5.21,5,1,6/17/2009,6/15/2009
118714/KB,963499661530,5,2,1,6/18/2009,6/15/2009,
118726/M  T,963499661541,5,1,1,6/16/2009,6/15/2009,
118717/DA,963499661552,0,0,1,6/16/2009,6/15/2009,
118716 DA/NCR  NICK W.,963499661563,0,0,1,6/16/2009,6/15/2009,
118664/JL,963499661574,21,37,1,6/18/2009,6/15/2009,
118664/JL,963499661585,21,37,1,6/18/2009,6/15/2009,
118706 JL/JL,963499661596,0,0,1,,6/15/2009,
118706 JL/JL,963499661600,0,0,1,,6/15/2009,
118721  DA/DA,963499661611,10,7,1,6/16/2009,6/15/2009,
118702 KB/KB,963499661622,14,7,1,6/16/2009,6/15/2009,
118700/KB,963499661633,15,7,1,6/16/2009,6/15/2009,
118573/DA,963499661644,0,0,1,,6/15/2009,
118727 DA/DA   DAVID G,963499661655,12,11,1,6/18/2009,6/15/2009,
118518/KB,963499661666,27,38,1,6/18/2009,6/15/2009,
118518/KB,963499661688,27,38,1,6/18/2009,6/15/2009,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
ssis packages reads the date values as text while reading from csv files. You should do proper conversions while transfering data.

The name of the file is also can be assigned dynamically by using expressions... take look at this link..
http://zulfiqar.typepad.com/zulfiqars_web/2006/11/ssis_dynamic_fi.html

also you can find detailed information in the link below..
http://www.bigresource.com/MS_SQL-Dynamic-File-Name-for-Flatfile-source-in-SSIS-uSiNhzqO.html
Wildone63,

Have you thought about having the FedEx Shipping System write directly to the database instead of a text file to eliminate this step you are trying to do?  

-Bear

Commented:
CSV file having problem some time in field terminator specially when there is Null value in databae
Better first save CSV file in Excel format. Check if all the required columns are there with specific data type specially look for Date Time data type.

Now import excel file directly or first make flat file from Excel by Tabl Delimited and Import flat file.

Author

Commented:
Thank You.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial