PDA

View Full Version : Handling multiple delimiters



presence76
03-08-2006, 04:11 PM
I have applications that use input files that have commas in them within the data. For example the number 10,000. When taking that down from an XLS to a CSV you run in to issues - namely "10" in one field and "000" in another. Because of this we decided to switch the delimiter on my machine to a |. This is fine. However, now we have other files coming in as .CSV that are delimited with commas. When I try to import, it does not work because my machine is looking for the | as a delimiter and not a ,.

Can you set multiple delimiters?

Is there another way around this issue?

Thanks in advance.

presence76
03-08-2006, 04:19 PM
I tried to change all , to ! but it said "formula too long"

XLGibbs
03-08-2006, 05:15 PM
You specify custom delimeters, or the typical Tab, Comma, space, or fixed width delimeters, with the option of quoted identifiers or consecutive delimeters.

A file the outputs 10,000 is not outputting a number, since the number value would only contain the comma in display (not in the actual value), so it would not matter if a number was displayed with a comma.

If the column contains 10,000 it would be contained as text and usually appear in quotes. However, if text contains commas, whoever exports the file into CSV format was certainly not thinking ahead.

You can also specify the columns manually. The only multiple delimeter scenario would be where two delimeters appear consecutively at each column break and is not likely ever done for many reasons.

Remember a number will not be sent to CSV containing a comma, only text could be.

presence76
03-09-2006, 05:16 AM
Maybe I misunderstood your reply. I have this CSV delimited with commas and when I try to open it in a XLS file, it does not work - it does not parse the fields into each column. I think it's because my default delimiter is set to | and not a comma. How can I bring this up to the XLS level? I could import into access, specify the delimiter and then unload to XLS but that would be time consuming. Is there a way around this within Excel?

If I misunderstood your reply, my apologies.

presence76
03-09-2006, 05:20 AM
I got it. Data. Import External Data. Import Data. Specify file and the Import Text Wizard appears.

Is there a way to write a macro to do this????

presence76
03-09-2006, 09:47 AM
OK. Now I have an edited XLS that I wish to save to a CSV file, however the delimiter must be a "," . How can I save this XLS down to a CSV with a comma as a delimiter without changing the default delimiter setting on my computer?

On the way up from CSV to XLS, there is a import text wizard that allows you to specify the delimiter. However, on the way down from XLS to CSV, there is not a Export Text Wizard for me to specify the delimiter.

Any suggestions?

presence76
03-09-2006, 12:20 PM
Allright. I have decided to read the csv as a text file but I'm not getting anywhere. I got an example from this site but for some reason it is not working past the first line.

Anybody know I/O for text files thru VBA

Should this go as a seperate post?

My code is

Sub textedit()
Dim ts As TextStream
Const FILE_PATH As String = "P:\Bank Finders Development\Copy of P3030900.csv"
'Dim tstream As t
Dim Fso As FileSystemObject
Set ts = Fso.OpenTextFile(LOG_FILE_PATH)
End Sub


When I compile I get "user-defined type not defined" with the

Dim ts As TextStream

Line. Any help is appreciated.

XLGibbs
03-09-2006, 03:06 PM
That means you are referring to a script host object without having set a reference to the script host..

You can either manually set the reference (Early binding) by going to

Tools>References and check the box for "Windows Script Host Object Model"

Or you can

Dim ts as Object
Set ts = CreateObject("Scripting.Dictionary")

I am not sure of the actual mechanics necessary for the code to import the file as text. I am also unsure why this is .csv issue is so troublesome. A .csv file by default is a comma delimeted file that when opened "looks like" excel.

Perhaps you should look into exporting as text instead of .csv to make things less troublesome.

smc2911
03-10-2006, 02:43 AM
Try Workbooks.Open "myfile.csv",Delimiter:=","
You can set any delimiter you want.

Sean.

presence76
03-11-2006, 10:56 AM
I have figured out how to read a text file, now I need to access a specific column on each record. Code below

Const FILE_PATH As String = "P:\Clients\Vanguard\Finance\20060117_ftp.DNO_Report_Summary.txt"
Dim ts As TextStream
Dim docname As String
Dim Fso As FileSystemObject
Dim txt$
Set Fso = New FileSystemObject
Set ts = Fso.OpenTextFile(FILE_PATH)
With ts

Do While Not .AtEndOfStream
txt = .ReadLine() + vbCrLf
docname = Column(1).Select

Loop
'Wend
End With
End Sub

Obviously, the docname =
line is wrong.

I need to access the field in the first column so I can compare it to a filename in another directory (which I think I have the code for)

Thanks for any help.

mdmackillop
03-11-2006, 11:23 AM
The Split function creates an array split at the Separator (Default is space)

If you have spaces as a separator try

docname = split(txt)(0)

or else, for comma

docname = split(txt,",")(0)

Regards
MD

presence76
03-11-2006, 11:41 AM
Works perfectly. Thanks alot!