converting excel to quoted text file

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

caznan

Thread Starter
Joined
Oct 21, 2009
Messages
10
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.
 

TerryNet

Terry
Moderator
Joined
Mar 23, 2005
Messages
80,923
I moved your thread because I think Business Applications is more appropriate than Networking.
 
Joined
Oct 20, 2004
Messages
7,837
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"
 
Joined
Sep 4, 2003
Messages
4,912
If Slurpee's suggestion doesn't resolve your issue please post a sample file and we'll get you taken care of.

Rollin
 
Joined
Oct 20, 2004
Messages
7,837
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.
 

caznan

Thread Starter
Joined
Oct 21, 2009
Messages
10
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?
 

caznan

Thread Starter
Joined
Oct 21, 2009
Messages
10
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???
 
Joined
Oct 20, 2004
Messages
7,837
Either Excel or OO will allow you to sae the file as you uploaded it as a CSV/text file, without needing a formula at all. I opened your file in OO and saved it as a CSV - see attached.
 
Joined
Sep 4, 2003
Messages
4,912
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

caznan

Thread Starter
Joined
Oct 21, 2009
Messages
10
By using the formula Slurpee55 suggested, converting it to a csv file, then opening it in Notepad....it worked and imported seamlessly.
Thanks
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top