Avatar of Amanda Walshaw
Amanda WalshawFlag for Australia asked on

Saving excel file to text comma between each field

I have to import data into an applicatin and the only way it will accep it is in txt field, I can copy and paste into text. how can i input ',' after each field and " " to incdicate string fields.

here is a sammple file

excel.xls
Microsoft Excel

Avatar of undefined
Last Comment
Saqib Husain

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
tflai

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Amanda Walshaw

That is fine, but where indicated on the file I need those " " on certain fields not all fields otherwise my import will not work.
ASKER
Amanda Walshaw

here is an entry that is correct for upload, how do I get only certain fields " "

0,"AM2010000","221 CHARMAN RD CHELTENHAM",1,0.00,19950117,20110701,19950117,<NULL>,1,FLGEN,GENERAL,FL00,<NULL>,5814,100,0,0,"",5899,0,1,0,20110705,1,0
Saqib Husain

You should remove the quotes from the excel file before saving as csv. It will automatically insert quotes when saving
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Amanda Walshaw

But I only need the quotes around these fields
,"AM2010000","221 CHARMAN RD CHELTENHAM",

when you save as csv and open in notepad it puts the quotes around everything, how do you keep the quotes I need and remove the other ones?

ie


"0,","""AM2010001"",","""CHELTENHAM DIR REVAL"",","1,","214000,","20010731,","20110701,","20010731,","<NULL>,","1,","FLGEN,","GENERAL,","FL00,","<NULL>,","5814,","100,","0,","0,",""" "",","5887,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010002"",","""LAIDLEY CREMATORIUM (LAND)"",","1,","0,","19961223,","20110701,","19961223,","<NULL>,","1,","CLGEN,","GENERAL,","CL00,","<NULL>,","3719,","100,","0,","0,",""" "",","69,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010004"",","""BUILDING REFURBISHMENT"",","1,","417694.92,","20090315,","20110701,","20090315,","<NULL>,","1,","FBIMP,","GENERAL,","FB00,","<NULL>,","3706,","100,","0,","0,",""" "",","6339,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010006"",","""SHELVING"",","1,","2949.47,","20050830,","20110701,","20050830,","<NULL>,","1,","FFGEN,","GENERAL,","FF00,","<NULL>,","1220,","100,","0,","0,",""" "",","5555,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010007"",","""AIR CONDITIONER"",","1,","338.44,","20050501,","20110701,","20050501,","<NULL>,","1,","PEGEN,","GENERAL,","PE00,","<NULL>,","2602,","100,","0,","0,",""" "",","5516,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010011"",","""SERVER"",","1,","5078.77,","20080207,","20110701,","20080207,","<NULL>,","1,","ITHSE,","GENERAL,","IT00,","<NULL>,","1220,","100,","0,","0,",""" "",","5993,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010013"",","""COMPUTER"",","1,","737.62,","20080512,","20110701,","20080512,","<NULL>,","1,","ITHPC,","GENERAL,","IT00,","<NULL>,","1220,","100,","0,","0,",""" "",","6077,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010016"",","""IPHONE"",","1,","729.13,","20101002,","20110701,","20101002,","<NULL>,","1,","OEMPC,","GENERAL,","OE00,","<NULL>,","1220,","100,","0,","0,",""" "",","6773,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010017"",","""PROJECTOR"",","1,","608.84,","20040701,","20110701,","20040701,","<NULL>,","1,","OEGEN,","GENERAL,","OE00,","<NULL>,","1220,","100,","0,","0,",""" "",","5348,","0,","1,","0,","20110701,","1,","0,"
tflai

How about save as in Text (MS DOS)?
ASKER
Amanda Walshaw

ok I will try that
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Amanda Walshaw

No that did not work either see result  Book1.txt

0      """AM2010000"""      """221 CHARMAN RD CHELTENHAM"""      """CORP"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010001"""      """CHELTENHAM DIR REVAL"""      """CORP"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010002"""      """LAIDLEY CREMATORIUM (LAND)"""      """CORP"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010004"""      """BUILDING REFURBISHMENT"""      """CORP"""      20110701      20110701      1      """S_005.00"""      $0.00
0      """AM2010006"""      """SHELVING"""      """CORP"""      20110701      20110701      1      """S_040.00"""      $0.00
0      """AM2010007"""      """AIR CONDITIONER"""      """CORP"""      20110701      20110701      1      """S_018.00"""      $0.00
0      """AM2010011"""      """SERVER"""      """CORP"""      20110701      20110701      1      """S_022.00"""      $0.00
0      """AM2010013"""      """COMPUTER"""      """CORP"""      20110701      20110701      1      """S_050.00"""      $0.00
0      """AM2010016"""      """IPHONE"""      """CORP"""      20110701      20110701      1      """S_014.29"""      $0.00
0      """AM2010017"""      """PROJECTOR"""      """CORP"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010000"""      """221 CHARMAN RD CHELTENHAM"""      """TAX"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010001"""      """CHELTENHAM DIR REVAL"""      """TAX"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010002"""      """LAIDLEY CREMATORIUM (LAND)"""      """TAX"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010004"""      """BUILDING REFURBISHMENT"""      """TAX"""      20110701      20110701      1      """D_005.00"""      $0.00
0      """AM2010006"""      """SHELVING"""      """TAX"""      20110701      20110701      1      """D_010.00"""      $0.00
0      """AM2010007"""      """AIR CONDITIONER"""      """TAX"""      20110701      20110701      1      """D_010.00"""      $0.00
0      """AM2010011"""      """SERVER"""      """TAX"""      20110701      20110701      1      """D_050.00"""      $0.00
0      """AM2010013"""      """COMPUTER"""      """TAX"""      20110701      20110701      1      """D_050.00"""      $0.00
0      """AM2010016"""      """IPHONE"""      """TAX"""      20110701      20110701      1      """D_030.00"""      $0.00
0      """AM2010017"""      """PROJECTOR"""      """TAX"""      20110701      20110701      1      """D_010.00"""      $0.00


what I need to see is the following

0,"AM2010000","221 CHARMAN RD CHELTENHAM",1,0.00,19950117,20110701,19950117,<NULL>,1,FLGEN,GENERAL,FL00,<NULL>,5814,100,0,0,"",5899,0,1,0,20110705,1,0
ASKER
Amanda Walshaw

I found the answer so easy.

Accept the data with the string fields and commas in excel or csv.
simply copy and paste into text all and all okay.

ASKER
Amanda Walshaw

my example worked
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Saqib Husain

I was working on this while you accepted the answer. You might like to try this macro which can save you a couple of steps.

Sub wcsv()
Dim rw As Range, cel As Range
Dim csvs As String, sfn As String
For Each rw In ActiveSheet.UsedRange.Rows
For Each cel In rw.Cells
csvs = csvs & cel.Value & ","
Next cel
csvs = Left(csvs, Len(csvs) - 1) & vbCrLf
Next rw
sfn = Application.GetSaveAsFilename("", "(*.txt),*.txt", 1)
Open sfn For Output As #1
Print #1, csvs
Close 1
End Sub