Tech Support Guy banner
Status
Not open for further replies.
1 - 12 of 12 Posts

·
Registered
Joined
·
10 Posts
Discussion Starter · #1 ·
I need to convert an excel file to a quoted comma-delimited format as a text file. When I use the formula:
=concatenate("""",A1,"""",",","""",B1,"""",",","""",C1,"""",), I get an error message "error 528'. I was told in a previous forum to use this formula in the first cell next to the numbers to be converted.
I have 1000 rows of 3 colums of numbers that have to read as follows:
"1","400100000311","52"
I can get it to a text format without quotes, but need the quotes as above.
I'm running windows 7, and open office.
Thanks for any help you can give me.
 

·
Registered
Joined
·
7,837 Posts
If you type a " into D1 (and drag it all the way down, so all 1000 rows have a " in D) and then enter this formula into a further column (E1 or wherever) I think you will get what you need:
=(D1&A1&D1&","&D1&B1&D1&","&D1&C1&D1)
(The & works to combine text - makes it easier to track than the concatenate formula and does the same thing.)

Oh, I assumed that A1 had 1 in it, B1 had 400100000311 in it, and C1 had 52 in it in order to get the final outcome of "1","400100000311","52"
 

·
Registered
Joined
·
7,837 Posts
I just reread post 1 - sometimes open office treats formulas differently than Excel, which I made that formula in, but it usually is just a preference for having semi-colons instead of commas in certain places. However, if it doesn't work for you, I have OO on my PC at home and can tinker with it there.
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #6 ·
I got to the point of creating it on the excel spreadsheet and the columns looked good; however, when I converted it to a csv file then to a text file, something was lost in the translation. I think I need steps.
Thanks for your work so far.
Maybe open office would work better?
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #7 ·
I used the formula, it looked right. Then I copied the quoted fields and pasted it on to notepad. That also looked OK, but it wouldn't import into the software program, saying "1" is an invalid number. I must have missed a step???
 

·
Registered
Joined
·
4,916 Posts
Here is a macro that can be run in Excel. Just change the text in red to reflect your desired save path and filename. The code assume the row 1 contains the first record and only the first three columns used (A,B,C) exactly as your sample shows. I've included the sample output from the file you attached earlier.

Code:
Sub SaveText()

Dim fso, OutputFile

Set fso = CreateObject("Scripting.FileSystemObject")
Set OutputFile = fso.CreateTextFile("[COLOR="Red"]C:\YourFilename.txt[/COLOR]", True)

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row

vString = Chr(34) & Range("A" & i).Value & Chr(34) & "," & _
Chr(34) & Range("B" & i).Value & Chr(34) & "," & _
Chr(34) & Range("C" & i).Value & Chr(34)

OutputFile.WriteLine (vString)

Next i

OutputFile.Close

End Sub
Rollin
 

Attachments

1 - 12 of 12 Posts
Status
Not open for further replies.
Top