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.
| Author |
Topic |
|
SMerrill
Posting Yak Master
206 Posts |
 Posted - 2002-05-15 : 15:34:59
|
| I started with a CSV file with 7, 8, or 9 columns in it. The record-types are mixed row for row. Based upon a tip I saw in this forum, I decided to import it into a table with only one column using Bulk Insert. The data was subsequently cleaned by applying several update queries to it. Now, I need to load it into the table it belongs in, which has several columns. (The first six columns are defined; the last three are named Arg1, Arg2 and Arg3 because some records have more arguments than others, and those arguments have different meanings per record.)Nonetheless, my current challenge is to port the one-column comma-delimited data into the 9-column destination table. Since I am using SQL 7, I am at a bit of a loss as to how to do this, except for the idea that I could build a query that uses SUBSTRING and PATINDEX or CHARINDEX to manually pull apart the columns by recognizing the commas. I also have (") quotes delimiting some string values between commas.I've been a database analyst long enough to know that there must be a better way! Can you lead me in the right direction?Thank you so much.--Shaun--SMerrillSeattle, WA |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-15 : 15:45:48
|
I'm pretty sure one of these will help:http://www.sqlteam.com/SearchResults.asp?SearchTerms=csvI would recommend that you NOT store three arguments in separate columns, but split them into another table, one argument per row:CREATE TABLE Arguments ( ParentID int REFERENCES ParentTable(ID),ArgumentNumber int NOT NULL,ArgumentValue varchar(20) NOT NULL )...cause you KNOW you're gonna need to support a fourth argument in the future!  OK, maybe not. But it's something you should consider. I'll admit that 3 extra columns isn't too bad, but if it's at all possible that it'll exceed 5 arguments, go with the separate table and join them. |
 |
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2002-05-15 : 16:28:47
|
| I found it rather easy to import a cleaned CSV file into a table using DTS. I was hoping that your answer would involve some hocus-pocus about exporting the CSV to a file again, then importing it back into a table with columns. Regarding the 7,8,9 columns, they won't add another, trust me. I have much experience behind that statement. What my schema needs is a table that has <null> values in the 7th, 8th and/or 9th columns if the data does not exist in that particular record.Still fishing . . . Shaun |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-15 : 16:45:30
|
quote:
I was hoping that your answer would involve some hocus-pocus about exporting the CSV to a file again, then importing it back into a table with columns.
Well, that's the long way around, I would think you'd want to avoid importing the same data twice...I sure would!Once you get it imported into the single-column table, you can try running this UPDATE statement on it:UPDATE myTableSET col1=col1 + replicate(',', 8-(Len(col1)-Len(Replace(col1,',',''))))Basically, this counts the number of commas in the column then add extras to pad it out to nine comma-separated columns. You can now export this straight out into a CSV file and then re-import it straight into the destination table. |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2002-05-15 : 17:01:28
|
 Ooooh! Now that's Hocus-Pocus!Thanks! |
|
|
|
|
|
|
|
|
Cleaning bad data