How to save data from a semicolon delimited file? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

How to save data from a semicolon delimited file?

Author

Topic

asiaenforcer
Starting Member

2 Posts

Posted - 2009-01-20 : 00:35:04

I am using SQL Server 2005 and BCP 9, I was able to create a format file, but having problems in saving all data into database.

Here is my sample data:
1;a;b;c;; 2;d;e;f;; 3;g;h;i;;
4;j;k;l;; 5;m;n;o;;
6;p;q;r;;

Here is my sample format file (BCP 9):
9.0
7
1 SQLCHAR 0 0 "" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 ";" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 ";" 3 s Latin1_General_CI_AS
4 SQLCHAR 0 0 ";" 4 t Latin1_General_CI_AS
5 SQLCHAR 0 0 ";" 5 u Latin1_General_CI_AS
6 SQLCHAR 0 0 ";" 6 v Latin1_General_CI_AS
7 SQLCHAR 0 0 "\r\n" 0 x Latin1_General_CI_AS

Problem is that when I used "\r\n", only 1, 4, 6 is save not all, but when I try to use "\0", only first record is save.
How I am gonna solve this problem?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-20 : 00:46:33

It's hard to know what to use in the format file without having the file in our possession. Have you tried like this:

bcp Db1.dbo.Table1 in C:\SomeFile.txt -c -t; -T -Sserver1\instance1 -r\r\n

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

asiaenforcer
Starting Member

2 Posts

Posted - 2009-01-20 : 03:15:06

quote:
Originally posted by tkizer

It's hard to know what to use in the format file without having the file in our possession. Have you tried like this:

bcp Db1.dbo.Table1 in C:\SomeFile.txt -c -t; -T -Sserver1\instance1 -r\r\n

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

what file are you looking for? Is it the data file? or the format file?
Actually the data that I have posted is not the actual data that I am working on ... here is the sample actual data (part of it) ...

087728127;V1.10;R;WithMedia;58-1231925646@208.74.75.250;0002;Wed Jan 14 09:34:06 2009;NA;Wed Jan 14 09:35:06 2009;0;Origination CANCEL; 487;O;000054;SIP;0067*116;116.93.51.23;01149614244656;208.74.75.250;5372a7cc31a45f9a237780a125ce14ca@116.93.51.23;116.93.51.23;14840;208.74.75.250;12348;G.711u_64k,G.711a_64k,RFC 2833;0;0;0;0;0;0;0;050500;SIP;3072151001;208.74.75.250;49614244656;208.80.156.52;58-1-1231925646@208.74.75.250;0.0.0.0;0;208.74.75.250;12350;NA;0;0;0;0;0;0;0;F;01149614244656;0;60;0;0;0000003A 496DB18E 484B6AB4 FA4B4AD0;;
087728128;V1.10;R;WithMedia;62-1231925689@208.74.75.250;0002;Wed Jan 14 09:34:49 2009;NA;Wed Jan 14 09:35:12 2009;0;Origination CANCEL; 487;O;050450;SIP;6326378682;116.93.51.98;011420556844118;208.74.75.250;call-00EC569B-CFE0-DD11-0216-4B6@116.93.51.98;116.93.51.98;11096;208.74.75.250;12364;G.729,RFC 2833;437;799;13988;25568;0;0;0;050001;SIP;6326378682;208.74.75.250;011420556844118;204.9.203.210;62-1-1231925689@208.74.75.250;204.9.203.210;11092;208.74.75.250;12366;G.729,RFC 2833;799;437;25568;13988;0;0;0;F;011420556844118;0;7;16;0;0000003E 496DB1B9 44AC3D38 FA4B4AD0;;

I am trying to save it to the database ... the file is in .CDR and has 59 columns, the last one is blank (;;) notice that for every ";;" has a space before it starts again, and some columns have blank spaces ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources