This is a really strange problem. I'm forced to read in CSV data as
fixed-length because the amount of data per row varies and they can't provide
additional commas to make a set number of columns. This works fine as I'm
just converting most of each row into a varchar column by replace commas with
CHAR(1)'s.

However, I've noticed that I'm either not reading in the entire row to insert
into the database or otherwise losing data from this import.

Import goes into a work table first, then to the actual DB table. Work table
has a varchar(2000) column to hold the data.


Example - I'll read in a CSV row with 21 entries. 1 gets stripped off for
another column - the others go into my varchar column with the replace as
above.

That same CSV file has another row with 17 columns. 1 stripped off, the
others to be inserted. However, in this case when I look at the database,
only 13 of those columns were inserted.



I'm stuck here. I'm not truncating data consistently at some actual column in
the text file. It works most of the time and will even fail on shorter
columns which leads me to believe there's something that's either not reading
the data correctly from the file or is truncating that row's data randomly
during the INSERT/SELECT SQL Command I'm running.

When I re-import the same file, I don't see any issues at all - can issue an
update to the desired column to fix the data, however this doesn't help me
overall because the data is operated on shortly after it's been inserted.


Am I missing something here? Has anyone experienced something similar?

Thanks in advance,

-Peter Schott

OK let me recap your scenario

You import a CSV into a table as fixed length.
That length is never > 2000 chars
You import to a work table.
Values in the worktable correct?
what does 1 column get stripped off for?

The data is either going missing here or in your move to the proper table
which?

Variable length rows is really ugly which is why this was written

http://www.databasejournal.com/featu...le.php/1462341


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Peter A. Schott" <pschott (AT) no (DOT) spamm.hear.drivefinancial.com> wrote in
message news:9f232095bpk9iditlius19nle1ho751nmc (AT) 4ax (DOT) com...

Agreed - variable length is ugly. That's why I had them make each row fixed
at 500 characters and am using the fixed-length type to access the file.

The first several columns I am using are fixed in position - no issues there.
It's only the remainder that are problems.

Basic scenario is that this is an image breakdown - category, page, rotation,
page, rotation, page, rotation, etc....

I am importing the last set of data into one large varchar column (2000 - made
big before we'd discussed lengths completely) from a max of 500 characters.
The first value is parsed out for the category. The rest go into the page
breakdown column in our database using REPLACE(col, ',', CHAR(1)) to replace
all of the commas with CHAR(1) - the character delimiter used by our
programmers.

At some point, that last step is losing data on random entries. I noted one
file that had about 10 page/rotation entries for one category and 5 for a
different category. The 5 entry row was truncated while the 10-entry row went
straight in. I've checked and re-checked the code, but it looks right to me.
If it were consistent at the last entry or the longest or at position 50, I
could understand, but it's seemingly random to me.

Any ideas? OT - I've been following a LOT of suggestions from your site
lately. They've been incredibly useful for what I'm trying to do. Now I have
to re-work a lot of items to process entire directories and stop depending on
time, but that's a completely different issue.


Thanks for the response.

-Pete Schott

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote: