September 3, 2010 at 12:18 pm
Hello All,
I am going to be able to import this format.
"A","Is this a ""valid"" file format"
"B","I don't ""think"" so"
My error is The column delimiter for column MyColumn was not found.
My flat file connection manager has " set as the text qualifier.
I have tried replace(MyColumn ,""","") in a data conversion.
Thaks so much to anyone who could help....
September 3, 2010 at 12:24 pm
Looks like you've just got nasty data issues. I would start with going after the knowns instead of the unknowns. Do a replace on ," with ,| then ", with |, . That gives you (strangely but functional) a column delimeter of (,) and a text qualifier of (|), letting you keep your quotes in the text itself and using two symbols that won't screw it up.
The problem with that is that if you have ", or ," inside the regular text field(s), it'll end up with some similar problems, just less of them. That might have to go to manual cleanup for the ones this catches inaccurately, but it'll do the bulk for you.
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
September 3, 2010 at 4:16 pm
I don't know about SSIS, but certainly you can read the file using OPENROWSET BULK
SELECT col1, col2 FROM OPENROWSET (BULK 'C:\sample.txt', FORMATFILE = 'C:\sample.fmt') AS Z
sample.fmt
7.0
3
1SQLCHAR00"\"" 0 Quote
2SQLCHAR00"\",\"" 1 col1
3SQLCHAR00"\"\r\n"2 col2
September 7, 2010 at 11:29 am
I am not very familiar with OPENROWSET BULK.
I also should have mentioned that not every record would have embedded quotes. The following would be more representative.
"A","Is this a ""valid"" file format"
"B","I don't ""think"" so"
"C","Nothing embedded"
Do you think OPENROWSET BULK would work given the additional sample data I have provided?
September 7, 2010 at 11:37 am
emily-1119612 (9/7/2010)
I am not very familiar with OPENROWSET BULK.I also should have mentioned that not every record would have embedded quotes. The following would be more representative.
"A","Is this a ""valid"" file format"
"B","I don't ""think"" so"
"C","Nothing embedded"
Do you think OPENROWSET BULK would work given the additional sample data I have provided?
How about pre-processing the file? Just replace "" with ' (for example) and then continue with , as column delimiter and " as text qualifier as you feed the file into your DF.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 7, 2010 at 11:46 am
emily-1119612 (9/7/2010)
I am not very familiar with OPENROWSET BULK.I also should have mentioned that not every record would have embedded quotes. The following would be more representative.
"A","Is this a ""valid"" file format"
"B","I don't ""think"" so"
"C","Nothing embedded"
Do you think OPENROWSET BULK would work given the additional sample data I have provided?
steve-893342 (9/3/2010)
I don't know about SSIS, but certainly you can read the file using OPENROWSET BULK
SELECT col1, col2 FROM OPENROWSET (BULK 'C:\sample.txt', FORMATFILE = 'C:\sample.fmt') AS Zsample.fmt
7.0
3
1SQLCHAR00"\"" 0 Quote
2SQLCHAR00"\",\"" 1 col1
3SQLCHAR00"\"\r\n"2 col2
Yes OPENROWSET BULK works absolutely fine using the statement I gave above
September 7, 2010 at 12:58 pm
How about pre-processing the file? Just replace "" with ' (for example) and then continue with , as column delimiter and " as text qualifier as you feed the file into your DF.
Is this typically done by calling an outside executable?
September 7, 2010 at 3:09 pm
I'd do it using a script task - no outside EXE needed. How big is the file likely to be, roughly? There is, of course, a performance overhead doing it this way and if your file is huge it might be better to try and deal with it all in one pass.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 8, 2010 at 9:13 am
How big is the file likely to be, roughly?
Less than a mb and I only need to process monthly so performance is not an issue. I have complained to my 'partner' and they may be able to fix this or more like provide an alternate file type. I'll followup if I need help with the preprocessing strategy. Thanks!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply