csv files

Question

csv files

agbondoc

Old Hand

Points: 366
More actions
January 4, 2011 at 2:36 pm

#229252

Hi I am new to this forum and somewhat new to sql.
The challenge I have is, I have a csv file with 50 columns and several of the columns contain multiple values separated with commas and other columns are separated by space.
I want to run a query that will separate these values w/out mixing other values from other rows.
Sorry if it sounds unclear.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

If there are values within a column containing commas, how do you know what values belong to one column and what values belong to the next/previous one?

Is there any chance for you to get the structure of the file change to use a column separator that is not used within the columns?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

agbondoc Old Hand Points: 366 More actions · Answer 2

Hi Lutz and thanks for the reply.

Im not clear on your question # 2, but for the other question there is a column that identifies each row. here is a sample of the column names.

Column1 = session# ( single value)

Column2 = instructorname (multiple values separated by comma)

Column3 = instructorID (multiple values separted by comma)

Column4 = hours (multiple values separted by comma)

Thanks

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

gecatoni (1/4/2011)
Hi Lutz and thanks for the reply.
Im not clear on your question # 2, but for the other question there is a column that identifies each row. here is a sample of the column names.
Column1 = session# ( single value)
Column2 = instructorname (multiple values separated by comma)
Column3 = instructorID (multiple values separted by comma)
Column4 = hours (multiple values separted by comma)
Thanks

What is the separator for Column 2 and Column 3? Is that where the space is? Or is it another comma?

If it's another comma or a space, you need to go back to the originator and ask for a different delimeter, like a pipe. I assume that Instructor Names can have spaces, anyway.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

agbondoc Old Hand Points: 366 More actions · Answer 4

hi,

column 2 and 3 are separated with commas'.

thanks.

agbondoc Old Hand Points: 366 More actions · Answer 5

hi,

Sorry, disregard my previous reply regarding columns 2 and 3, i misunderstood the question.

Columns 2 and 3 are separated by "pipe delimited". this is also true for all 50 columns. Now, the values in column 2 and 3 are separated by a comma and there are values in other columns that have "space" as a separator.

Thanks

WayneS SSC Guru Points: 95460 More actions · Answer 6

You will first have to load the csv file into a staging table. You can do this by creating a #temp table with 50 appropriately sized columns, and then using BULK INSERT, specifying a column delimiter of "|".

If you still need to separate "sub-columns" with comma delimiters, then you would need to utilize the DelimitedSplit8K function upon those columns in your staging table. Here is the latest version of the Delimited Split Function

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

agbondoc Old Hand Points: 366 More actions · Answer 7

Thanks Wayne, I will try your latest version of DelimitedSplit8k. I will keep you posted on my progress... wish me luck...