Bulk Inserting a .dat file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Bulk Inserting a .dat file

Author

Topic

csam
Starting Member

4 Posts

Posted - 2007-08-19 : 15:29:43

The following produces no errors, but does not place any rows in the table:

USE MoneyManager
GO
BULK INSERT DailyPrices
FROM 'C:\Downloads\Securities\s20070105.dat'
WITH
(
firstrow = 2,
formatfile = 'C:\Downloads\DailyPrices.fmt',
errorfile = 'c:\downloads\price_error_file.txt'
)

select *
from DailyPrices

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-19 : 15:34:14

Maybe you can post an excerpt of the "s20070105.dat" file?

E 12°55'05.25"
N 56°04'39.16"

csam
Starting Member

4 Posts

Posted - 2007-08-19 : 17:56:33

Thank you for replying. This is s20070105.dat if opened with Notepad. The file is small so I have included everything.

2007-01-05 Security File For Main Office 240
125325407~CGMRX~CGM TR REALTY FD~REALTY FD~F~~Y~00C~~26.65~~~~~~~~
172967101~C~CITIGROUP INC COM~COM~S~~Y~003~~54.77~~~~~~~~
189054109~CLX~CLOROX COMPANY COM~COM~S~~Y~003~~63.89~~~~~~~~
244199105~DE~DEERE + CO COM~COM~S~~Y~003~~90.89~~~~~~~~
313400301~FRE~FEDERAL HOME LOAN MTG CORP COM~COM~S~~Y~003~~66.91~12/19/1990~~~~~~~
313586109~FNM~FANNIE MAE COM~COM~S~~Y~003~~59.57~12/14/1990~~~~~~~
316066208~FDEQX~FIDELITY CAP TR DISCIPLND EQTY~DISCIPLND EQTY~F~~Y~009~~28.73~~~~~~~~
316153105~FFIDX~FIDELITY FD INC COM~COM~F~~Y~00C~~35.6~~~~~~~~
413838103~OAKMX~HARRIS ASSOC INVT TR OAKMARK FD~OAKMARK FD~F~~Y~999~~45.7~~~~~~~~
458140100~INTC~INTEL CORP COM~COM~S~~Y~003~~21.1~04/18/2000~~~~~~~
459200101~IBM~INTL BUSINESS MACHINES COM~COM~S~~Y~003~~97.42~12/19/1990~~~~~~~
476293105~PGNAX~JENNISON NAT RES FD INC CL A~CL A~F~~Y~009~~41.91~~~~~~~~
478160104~JNJ~JOHNSON + JOHNSON COM~COM~S~~Y~003~~66.62~12/19/1990~~~~~~~
494368103~KMB~KIMBERLY CLARK CORP COM~COM~S~~Y~003~~68.44~~~~~~~~
524901105~LM~LEGG MASON INC COM~COM~S~~Y~003~~97.19~~~~~~~~
560636102~MPGFX~MAIRS & POWER GROWTH FD INC COM~COM~F~~Y~00A~~76.99~~~~~~~~
573012200~MGRIX~MARSICO INVT FD GROWTH & INCOME FD~GROWTH & INCOME FD~F~~Y~~~20.06~~~~~~~~
640079109~NP~NEENAH PAPER INC COM~COM~S~~Y~003~~35.28~~~~~~~~
683924104~OPPAX~OPPENHEIMER GLOBAL FD SH BEN INT A~SH BEN INT A~F~~Y~009~~73.22~~~~~~~~
792860108~STA~ST PAUL TRAVELERS COS INC COM~COM~S~~Y~003~~52.41~~~~~~~~
808509731~SWHFX~SCHWAB CAP TR HEALTH CARE FOCUS FD~HEALTH CARE FOCUS FD~F~~Y~999~~15.04~~~~~~~~
884116104~TAVFX~THIRD AVENUE TR VALUE FD~VALUE FD~F~~Y~009~~59.31~~~~~~~~
911476802~UNWPX~UNITED SVCS FDS WORLD PRECIOUS MINERALS~WORLD PRECIOUS MINERALS~F~~Y~~~25.34~~~~~~~~
921908109~VGENX~VANGUARD SPECIALIZED PORTFOL ENERGY PORTFOL~ENERGY PORTFOL~F~~Y~009~~61.25~~~~~~~~
921938106~VWINX~VANGUARD/WELLESLEY INCOME FD COM~COM~F~~Y~00C~~21.77~~~~~~~~
922031208~VWEHX~VANGUARD FIXED INCOME SECS FD HI YLD CP PTFL~HI YLD CP PTFL~F~~Y~00D~~6.22~~~~~~~~
949917520~STHYX~WELLS FARGO ADVANTAGE FDS HIGH INCOME INV CL~HIGH INCOME INV CL~F~~Y~00D~~7.81~~~~~~~~
984121103~XRX~XEROX CORP COM~COM~S~~Y~003~~16.73~~~~~~~~
013817101~AA~ALCOA INC COM~COM~S~~Y~003~~28.76~08/25/2004~~~~~~~
015565765~AHSAX~ALGER FD HEALTH SCI FD CL A~HEALTH SCI FD CL A~F~~Y~~~17.26~~~~~~~~
025816109~AXP~AMERICAN EXPRESS CO CM~CM~S~~Y~003~~59.13~12/14/1990~~~~~~~
03076C106~AMP~AMERIPRISE FINL ICN COM~COM~S~~Y~~~55.5~~~~~~~~
17275R102~CSCO~CISCO SYSTEMS INC COM~COM~S~~Y~00F~~28.47~11/01/2001~~~~~~~
20030N101~CMCSA~COMCAST CORP COM CL A~COM CL A~S~~Y~~~42.55~~~~~~~~
23337M107~KHYAX~DWS HIGH INCOME SERIES HIGH INCOME FD CL A~HIGH INCOME FD CL A~F~~Y~00D~~5.45~~~~~~~~
302570AN6~~FPL GROUP CAP INC CO GUARNT CALL@>PAR/MAKE 6.125% ~CO GUARNT CALL@>PAR/MAKE WHOLE~B~05/15/2007~Y~2~6.125~100.169~~~1000~11/15/2001~05/11/2001~~~10
3133X06Q7~~FEDERAL HOME LOAN BANK US GOVT AGENCY NONC 4.125% ~US GOVT AGENCY NONCALLABLE~B~08/13/2010~Y~2~4.125~97.625~~~1000~02/15/2004~07/24/2003~~~10
3133X4ZM6~~FEDERAL HOME LOAN BANK BONDS CALLABLE SER K 3.00% ~BONDS CALLABLE SER KE08~B~03/24/2008~Y~2~3~97.563~~~1000~09/24/2004~03/24/2004~03/24/2006~100~10
32022LAE0~~FIRST FIDELITY BANK C.D. NONCALLABLE 5.25% ~C.D. NONCALLABLE~B~06/07/2007~Y~12~5.25~99.939~~~1000~~06/07/2006~~~10
341602XZ8~~FLORIDA ST DEPT GEN SVCS DIV REV NONCALLABLE R 5% ~REV NONCALLABLE REG SER A~B~09/01/2010~N~~5~104.525~~~1000~03/01/2006~08/15/2005~~~10
34160WJL9~~FL ST DEPT ENVIRNMNTL PROTN PR REV NONCALLABLE 5% ~REV NONCALLABLE REG SER A~B~07/01/2011~N~~5~105.357~~~1000~07/01/2004~03/01/2004~~~10
348172LD7~~FORT MYERS FLA UTIL REV REF REV NONCALLABLE 0.00% ~REV NONCALLABLE BK ENTRY SER A~B~10/01/2014~N~~0~73.295~~~1000~~08/12/1993~~~10
370425RU6~~GENERAL MOTORS ACC CORP NOTES NONCALLABLE 7.25% ~NOTES NONCALLABLE~B~03/02/2011~Y~2~7.25~103.662~~~1000~09/02/2001~03/02/2001~~~10
370425SD3~~GENERAL MOTORS ACC CORP NOTES NONCALLABLE 6.125% ~NOTES NONCALLABLE~B~08/28/2007~Y~2~6.125~99.985~~~1000~02/28/2003~08/29/2002~~~10
38142Y815~GSLAX~GOLDMAN SACHS TR LARGE CAP VAL FD CL A~LARGE CAP VAL FD CL A~F~~Y~009~~14.3~~~~~~~~
437076AM4~~HOME DEPOT INC NOTES CALL@MAKE WHOLE 4.625% ~NOTES CALL@MAKE WHOLE~B~08/15/2010~Y~2~4.625~97.619~~~1000~02/15/2006~08/11/2005~~~10
44929K507~ICENX~ICON FUNDS ENERGY FD~ENERGY FD~F~~Y~009~~29.81~~~~~~~~
44929K705~ICHCX~ICON FUNDS HEALTHCARE FD~HEALTHCARE FD~F~~Y~00E~~16.65~~~~~~~~
469485EW2~~JACKSONVILLE FLA SALES TAX REV NONCALL BOOK 3.00% ~REV NONCALL BOOKENTRY~B~10/01/2009~N~~3~97.634~~~1000~10/01/2003~02/19/2003~~~10
58155Q103~MCK~MCKESSON CORP COM~COM~S~~Y~003~~51.31~~~~~~~~
68002Q412~OLCPX~OLD MUTUAL ADV FDS II LARGE CAP GRW CONCENTRATE CL~LARGE CAP GRW CONCENTRATE CL Z~F~~Y~~~18~~~~~~~~
68380U100~OSMAX~OPPENHEIMER INTL SMALL CO FD CL A~CL A~F~~Y~009~~25.99~~~~~~~~
72369G108~PGHYX~PIONEER FUNDS GL HI YLD CL A~GL HI YLD CL A~F~~Y~00D~~12.45~~~~~~~~
77954M105~PRWCX~ROWE T PRICE CAP APPRECIAT F SH BEN INT~SH BEN INT~F~~Y~009~~20.55~~~~~~~~
77954Q106~TRBCX~ROWE T PRICE BLUE CHIP GROWT FUND~FUND~F~~Y~009~~35.69~~~~~~~~
88579Y101~MMM~3M CO COM~COM~S~~Y~003~~77.42~~~~~~~~
912828AP5~~US TREASURY N/B US GOVT NATIONAL NONCALLABL 4.00% ~US GOVT NATIONAL NONCALLABLE~B~11/15/2012~Y~2~4~96.848~~~1000~05/15/2003~11/15/2002~~~10
912828BM1~~US TREASURY N/B US GOVT NATIONAL NONCALLAB 3.125% ~US GOVT NATIONAL NONCALLABLE~B~10/15/2008~Y~2~3.125~97.219~~~1000~04/15/2004~10/15/2003~~~10
912828EB2~~US TREASURY N/B US GOVT NATIONAL NONCALLAB 3.875% ~US GOVT NATIONAL NONCALLABLE~B~07/31/2007~Y~2~3.875~99.359~~~1000~01/31/2006~08/01/2005~~~10
91324P102~UNH~UNITED HEALTH GROUP INC COM~COM~S~~Y~001~~52.55~~~~~~~~
9ZZZFD104~MMDA1~TD BANK USA, NA MMDA FDIC INSRD,NOT COVERED BY SIP~FDIC INSRD,NOT COVERED BY SIPC~S~~Y~~~1~~~~~~~~
9ZZZZZ004~ZZZ00~RESERVE FUND (HELD AT RESERVE) PRIMARY FUND CLASS ~PRIMARY FUND CLASS R~S~~Y~~~1~~~~~~~~
9ZZZZZ327~ZZZ32~RESERVE FUND (HELD AT RESERVE) FLORIDA PORTFOLIO~FLORIDA PORTFOLIO~S~~N~~~1~~~~~~~~
0000063 TOTAL RECORDS

And this is the format file DailyPrices.fmt

8.0
18
1 SQLCHAR 0 9 "\t" 1 Cusip SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 8 "\t" 2 Symbol SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 60 "\t" 3 Sec_Name SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 30 "\t" 4 Description SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "\t" 5 Type SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 24 "\t" 6 Mat_Date ""
7 SQLCHAR 0 1 "\t" 7 Tax_Code SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "\t" 8 Div_Int_Freq SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 5 "\t" 9 Coupon_Rate SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 41 "\t" 10 Price ""
11 SQLCHAR 0 24 "\t" 11 Price_Date ""
12 SQLCHAR 0 20 "\t" 12 Factor SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 9 "\t" 13 Par_Value SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 24 "\t" 14 First_Coup_Date ""
15 SQLCHAR 0 24 "\t" 15 Issue_Date ""
16 SQLCHAR 0 24 "\t" 16 Call_Date ""
17 SQLCHAR 0 41 "\t" 17 Call_Price ""
18 SQLCHAR 0 7 "\r\n" 18 Bond_Xplier ""

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 04:21:58

In the format file, you have stated that TABS are delimiters between columns.
But in the text file, ASCII 126 seems to be the current delimiter.

E 12°55'05.25"
N 56°04'39.16"

csam
Starting Member

4 Posts

Posted - 2007-08-20 : 09:10:01

Changing the delimiter in the format file from "\t" to "~" produces the same result. The last delimiter in that file remains "\r\n".

The tab delimiter works for me with another file in the .txt format, but I am trying to eliminate the extra process of converting from the .dat format in which they are downloaded to .txt files before bulk inserting.

Thank you for your assistance.

csam
Starting Member

4 Posts

Posted - 2007-08-22 : 23:18:38

Peso-
My last reply to you was incorrect. At any rate I took care of the problem by creating a little utility to convert the downloaded file's delimiter to '\t' then bulk insert with the appropriate format file.
Ruel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources