[RESOLVED] VB6 & Reading CSV file with data including spaces

Nothing prevents you from using Redim Preserve on the Fields array to make more room for fabricated fields, or even using a second array with more elements and copying everything into it. But if you are creating XML output I don't see where that even matters.

Oh well, to answer your question:

Code:

---

SELECT field1, field2, ... INTO [newfile.txt] FROM [origfile.txt]

---

There field1, field2, etc. are the names of the fields from origfile.txt or the expressions based on those fields. You will want to name expressions or they get names like Exp1 or something so use the AS name clause for them.

If you have Office or at least Access installed you might look for the Help file:

C:\Program Files\Common Files\microsoft shared\OFFICEnn\1033\JETSQL40.CHM

... where the nn matches the version of Office you have installed.

For your benefit (and the benefit of anyone else reading this thread) I have removed earlier attachments that had a subtle but serious flaw.

The "cleaning" was being done using the Shell Lightweight API entrypoint StrSpn. This expects the buffer that it scans to be NUL-terminated, however I was not making sure this was always true.

I have corrected this, though now it runs a little slower. VB6 binary I/O relies on the target variable of a Get statement to know what size to read. That means you can't preallocate a Byte array to be "size to read plus one" and just read "size to read" bytes into it.

So there is a Redim Preserve after the Get now, which isn't the fastest way to go, but it is less complex than adding API I/O into the mix now.

Of course if you want to do that (it isn't difficult and lots of sample code is around, perhaps even an MS KB article) you can always go ahead. Then you could allocate the buffer as 1 byte larger than the chunk to read, which covers the issue without any Redim Preserve of the buffer. That can gain a little speed back, though not a huge amount.

Thanks dilettante; for your continuous help and support.
I am checking your cleaner and I have came across a problem; I am unable to figure out the issue.

I am saving a excel file into .csv format with tab deliminator (as excel file may contain a "," with text so to avoid that before cleaning I am using tab deliminator).

Code:

---

objXlBook.SaveAs csvFileName, XlFileFormat.xlCurrentPlatformText

---

All is well for most of the tested files and your cleaner was giving current results.

Now one of the sample I am attaching here for you (I have PM you the zip password), is not giving me correct output.

This is the settings I have changed in your code

Code:

---

.CleanChars = " .0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()+-:"

---

On this line I have changed from Comma to Tab

Code:

---

If Buf(BufNextChar) = 9 Or Buf(BufNextChar) = 13 Then '44 = ","  13 = CR    9 = Tab

---

Any other changes required?

After running your cleaner against this sample file you will see at the output line 7561 you will find the problem; It is skipping one ","

Result
777-0000-000,SSS ACBXHHJ,300.000,CFABCFRUXXX,7910013233LM MARBLE AND GRANITE,OTHERS

Should Be
777-0000-000,SSS ACBXHHJ,300.000,CFABCFRUXXX,7910013233,LM MARBLE AND GRANITE,OTHERS

Thanking you in advance for your kind help,

Best Regards,

Sam

you boys are having fun...

are you trying to remove all of the & and all of the / from everywhere in the data?

And I mean evrywhere, every fields every string?

here to help

There seem to be quite a few "illegal" characters he needs to clean out.

Ok, one... more... time.

This one fixes a bug introduced in CleanCSVReader.cls by the last change which addressed another bug where buffers needed a NUL terminator.

Whew!

See if this corrects the problem without creating any new ones.

Thanks a lot dilettante, It worked :)

Thanks again; will test out few more,

I have changed this
Private Const Delim As Byte = 9 '44 =comma 9 = Tab

and

.CleanChars = " .0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()+-:"

Best Regards,

Sam

Thanks incidentals for your interest; yea we need to remove everything other than these characters .0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()+-: as the system we are going to pass data into won't accept other than these characters.

Best Regards,

Sam

Hi dilettante,

Some issues with the cleaner I am attaching the Sample .csv file which results error.
Looking forward to hear from you.

Best Regards,

Sam

do you have a set of unacceptable characters rather than acceptable ones, or can you see how to generate the set?

just off te top of my head I can see a process that captures valid entities and writes them out as a new valid file its a single pass I think using REGEX

can look into that if you want?

Thanks incidentals, just for your info

These are acceptable character set " .0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()+-:" other than these are not allowed.

I am passing a .csv file (tab delimited) and all the other character excluding above character set are to be cleaned.

Best Regards,

Sam

Regex would probably be quite slow, which is no problem for small files but when they get big you'll notice.

Tab-delimited files are not CSV (literally it means "comma separated value") and should normally be named using a file extensions such as .TXT or .DAT. This isn't critical but calling them .CSV can be confusing.


Ok, the program was choking because you had fields over 60 characters long after cleaning. Pretty weird, but I suppose it happens. However if you have long runs of prose as column values the odds are high that you may have comas within the text. In such a case you'll want to add quotes (") to such a columm before calling CSVWriter.PutRecord, and to do that you'd want to replace all " by "" to escape the quotes which might be in long text too. It gets even uglier if the text can contain CR, LF, CRLF, and there isn't a great cure for that in a CSV-formatted file.


This version handles field values of any length. It also has your Delim = 9 (Tab instead of comma) and your .CleanChars set, though you might still want to change the filenames of the dirty and cleaned files.

Thanks a ton dilettante for your support again and again,

will check this out,

Best Regards,

Sam

Hello dilettante,

A quick update, the cleaner is working great :) no issues till now
Again thanks a lot, it has been really a great support.

Wish you great success,

Best Regards,

Sam