| Author |
Topic |
|
kotonikak
Yak Posting Veteran
92 Posts |
 Posted - 2014-11-04 : 15:57:14
|
| I have a really messy text file with a vertical bar delimiter like so:PL|2015|P10|614016|0772262|987|13|582139002|2|C|BRINSON||||||N|IL|N|||| CRLFPL|2015|P10A|614016|5148|516 BRINSON||BRINSON|GA|39825||||||| CRLFPL|2015|P10B|614016|1|260176893|1|I||HEARD|GLENN||||516 COLQUITT||BRINSON|GA|39825||||||||N|N|| CRLFEach row data can have different columns, so one row can have 5 columns whereas another can have 20. When I open the text file, I get a CRLF rowterminator after each row (I added those to each row for display purposes). Because the column size is not the same, I have no idea how to import into SQL. The point to this is to eventually import to SQL and then for each row whose third column is P10, import that into a different table, for each row with P10A, import to a different table, etc. (Every row whose third column is P10 has the same amount of columns, etc) What's the best way to tackle this issue? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-04 : 15:58:57
|
| Use BCP and specify the delimiters or SSIS with the BulkCopyTask that does the same thing. |
 |
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2014-11-04 : 16:09:27
|
| Not too familiar with bcp and I can't use SSIS as I don't have it (and have no idea how to use it either). Any bcp examples that could be useful? Will this just create columns with null data if the amount of columns exceeds what's in each row? |
|
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2014-11-04 : 16:51:51
|
Tried using the following command:bcp database.dbo.tablename in "R:\...\filename.txt" -T -c -Sservername Says 0 rows copied. My table has 76 columns but I'm not quite sure what the max column number is. What's a way to tell in Notepad or anywhere else? The amount of columns differs for each row. Will using the bcp command prompt enter NULLS for those rows with a small amount of columns? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-04 : 17:35:53
|
| Bcp's default is tab delimited by you have a bar in your sample. You need to specify the delimiterThere's an option for empty columns. The default is to set them to null iirc |
|
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2014-11-04 : 18:23:29
|
Updated the code:bcp database.dbo.tablename in "R:\...\filename.txt" -T -c -Sservername -t^| -r\r\n Now I'm getting the following error: (I also updated the table to be 100 columns)Starting copy...SQLState = S1000, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file0 rows copiedTried to check what this means and not quite sure... |
|
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2014-11-04 : 18:28:16
|
| I feel like the code above would do the same thing as bulk insert. How would it handle those rows that say have 10 columns? The rowterminator is right after the 10th column. Will it add NULLs to those additional 90 columns and then move on to the next row? Just a little confused. Not sure if that's what's giving me the above error. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-05 : 09:08:38
|
| BCP expects the same number of fields per row on the input file. Even if those fields are empty. If your file looks different, you'll want to look at a SSIS solution instead, which you can set up to provide the missing columns with a small script component |
|
|
|
|
Column size issue when importing data