Csv File Format: Iso-8859-1 Charset ?

Hi,

I am trying to use VBA to import data from an online web service into Excel (using winhttp).

The returned result is encoded in XML so I use Win XML to manage it. So far so good.

My problem is that the returned XML data (text) is encoded in ISO-8859-1 meaning I get weird things like "&" in the text. So my question is, how can I convert the encoded characters into plain text (UTF-8?) before I write it into the cells in excel?

The returned xml string has a header like this <?xml version="1.0" encoding="utf-16" standalone="yes"?>, one "bright" idea I had was to convert the "utf-16" into "ISO-8859-1" before passing it into "xml_doc.Loadxml (xmlString)" - but that did not help. Any ideas?

Hank

I have a sheet in Excel which I want to save as a UTF8 format text file. I previously thought it couldn't be done but I found the below and it definitely works in saving a text string as one:

Code:

Code:

'Function saves cText in file, and returns 1 if successful, 0 if not
Public Function writeOut(cText As String, file As String) As Integer
On Error GoTo errHandler
Dim fsT, tFilePath As String

tFilePath = file + ".txt"

'Create Stream object
Set fsT = CreateObject("ADODB.Stream")

'Specify stream type – we want To save text/string data.
fsT.Type = 2

'Specify charset For the source text data.
fsT.Charset = "utf-8"

'Open the stream And write binary data To the object
fsT.Open
fsT.writetext cText

'Save binary data To disk
fsT.SaveToFile tFilePath, 2

GoTo finish

errHandler:
MsgBox (Err.Description)
writeOut = 0
Exit Function

finish:
writeOut = 1
End Function

What I need to do is adapt the references to cText to save an entire named Excel worksheet in UTF8 format, instead of that single string of text. I would like to think this is quite straight forward, but my initial attempts haven't been successful.

Thank you greatly, as always, in advance.

Hi Everyone. I have a tab-delimited file that I open with Excel, make a few tweaks to, then save as a comma-delimited file. However, the export file has to use quotes as a text qualifier as well, and I can't seem to find an option to add the quotes automatically when saving as a CSV file. Does anyone know how to achieve this. I must admit it has been many years since I have used Lotus 1-2-3, but I seem to remember it being quite easy to do in that program.

Since quotes are used quite commonly as a text qualifier in a delimited file, I find it hard to believe that Excel would not support this feature, but I can't find it if it does.

I don't have to do this that often, but often enough that I would really like to find a solution that doesn't involve me opening the CSV file and manually adding the quotes where necessary.

Any help would be much appreciated.

Alan

Hi all,
I have managed to get to some extend with vba and internet explorer automation, but despite doing further research I am just not sure how do I go about clicking and image button to submit with ******* option. Please see below:

Code:

 
	 HTML Code:


	
<form name="form1" method="post" accept-charset="iso-8859-1" acceptcharset="iso-8859-1" ACTION="/JP" onSubmit="return LockPayButton()">
[b]<input type="hidden" name="submitOption" value="">[/b]
<input type="hidden" name="o" value="mpce">
<input type="hidden" name="p" value="mpcr">
<input type="hidden" name="motoId" value="0">
..
..
<input type="image" [b]*******="javascript:document.form1.submitOption.value='purchase'"[/b] src="aac.gif" border="0">
</form>

I am not particularly sure how do I go about manipulating this button to click. Any help is much appreciated. Thanks!!

Bonjour

J'ai le problème suivant.
En exécutant une fonction mathématique sur une cellule contenant un nombre,
que ce soit directement "=5+6" ou "=5*6" ou indirectement SOMME(C3:C7)
me renvoie un zéro. Les cellules n'ont pas de texte en guise de nombre (vérifié).

Avez vous une idée de l'option qui est incorrectement paramétrée ?

Cordialement,
F.

I need to save an excel spreadsheet with approximately 20 columns of info to a txt file. the problem is that i need the information in the file to be saved without tabs autmoatically being inserted into the text file. I need the information to run continuously.

for ex:
i have on my spreadsheet:
123 xyw text abc

i need it to be saved to a text file and when i open the text file the info must appear as
123xyztextabc

Is there any format i can save the sheet to to get these results or is there any other way around it?

thanks!

Is it possible to restrict the file format options available to a user when saving an Excel file? I have an Excel file which needs to be filled in and saved as only a CSV file. Is there anyway I can force this option.

Thank You in Advance
SR

Hello,

I need your help creating a Macro, of a click of a button,

Here what I'd like to happen:

1. A "Save As" prompt will come up asking me where to save and export the txt file to.

2. Excel will then gather info from a single row that I have selected, and export it into a txt file like the format below ex.

Name: A1

Phone: A2

Address: A3

Company: A4

Item: A5

Any help is greatly appreciated

Thanks a bunch

Jay

Hello,

In my excel file I have chinese and some other special characters and many cells have text with commas.

My problem starts when I try to save my file as a Text.
When I Save As with the Unicode option I can see the chinese characters in my text file but also I see a lot of quotes """ because I have commas in my xls file.
When I Save As with Tab Delimited option to solve the comma problem, the chinese characters become ????

It seems that cannot be possible to do it manually cause there is no option to Save As with Unicode and Tab option together.

So I would deeply appreciate someone could give me a solution with VBA code to save my file as Text with Unicode and Tab Delimited option.

thanks in advance for your kind help.
regards,

PS. I searched the forum but I haven't found a situation similar to mine.

Hi,

I have a range of data which I'm trying to save into a comma separated .txt file. Excel only allows one to save to a comma separated .csv file. Unfortunately the program I am trying to upload to requires comma separated .txt file.

I tried save to .csv file then opening in wordpad and saving as .txt file but unfortunately this means some of the blank fields are lost.

Please see attached file where the range of data I wish to export to comma separated .csv file is cells B1 : CI36. Further column T (Date) needs to be saved to .txt file in text format ddmmyyyy (i.e.: no "/").

When opening the .txt file the first line of the attached file would read:
,PYMNT12MAY11,,01,,RIV001,,,,,,,56001811149035,,,GBP,8640,,12052011,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Is the best way to write a macro to export the data?

Kind regards,
Sky

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"> Hello,

I have GPS data in an excel sheet (CSV) that I download from my GPS data logger. I format this data and use IF/THEN formulas to calculate lengths of time. I already have a macro set up that puts these formulas in the first couple rows, to the right of the columns that the GPS data is in, then i manually "fill down" the formulas until the last row w/ GPS data), but would like some VB code that I can add to my macro to automate the whole thing.

Also since the data from the logger can only export as a CSV file, I need some code that can save the file as another file type (i.e.excel workbook, PDF etc...) in the same folder that the original CSV file was opened from. (i.e. if I open Data.csv from C:\example, then it would also save to C:\example).

Please note that the name of the CSV file downloaded from the GPS device, as well as the folder its exported to will change every time.

If any samples are required, please don't hesitate to email me at dvictoriano@bellsouth.ne t .

Let me know if you need any additional info and thank a lot in advance.

I think (hope) this is the proper place for this particular question. I have created a macro that does a lot of fancy things eventually opening a "Save As" window to save the results as a CSV text file. This should be a nice clean text file to be uploaded to a "LMS" Learning Management System (Blackboard). The catch is the instructor only uses a Macintosh (OS-X). When he attempts to upload the file the "LMS" says it isn't in the correct format. I think it's a CR/LF issue, because when I open the file in a pure text editor, I see a bunch of unprintable characters. Any suggestions as to how to export a nice clean (PC compatible) file from Excel Macintosh?

THANKS

I am having dificulty finding a way of saving a file in the format I need
for another application.
I have a file which has a number of fields enclosed in quotes and seperated
by commas. Some of these fields are text and some numeric though treated as
text, eg phone numbers. I can open the file with Excel, make changes I need
to but when it comes to saving I cannot figure out how to get the same
format again. It has to be comma seperated with each field in quotes and
carriage return at the end of a record.

Thanks for any help.

ChrisK

I am creating a comma delimited (text) file for upload to another
application. The file is a tab in Excel (2000). No matter what (.txt) file
format I select to save as, each line of the output file is in quotes (which
are not present in the excel source file. As a result, the upload
application doe not recognize the data.

Hi,
When i use the Pl/sql developer function 'copy to excel', it creates/opens
an excel file for me in the "c.\...\temp" directory".
Now when I am in this file and I use the file/open and file/save commands excel defaults to the "c.\...\temp" directory".

I was wondering if anyone knows how to change the path of the file/open file/save as commands.
I would like to be able to save the file right at the "c:\" directory.

I am using excel2000 and windowxp.
If you need any more info, just ask. I am not very computer literate.

Thanks

Is there a way I can open an xlsx file and save it as a xlt file without playing around with the string I use to save the file. i.e. can you specifiy the file format when saving the file (using SaveAs) to xlt?

thx

I'm doing my file save like this:
ActiveWorkbook.SaveAs Filename:="C:\MyFile.txt"
and I get all kinds of stuff I don't want to see. It looks like Excel is saving my file as an xls that just happens to have a txt file extension. Can someone tell me (a VBA idiot) how to do this save in txt format?

Thanks

Hello all,

I regulary receive from a client a data transfer file that I must open in Excel to edit and then save as a Tab-delimited file for importing into a database. If a field has a comma in it, such as

123 45th St., Apt. 18

then Excel saves the field in the Tab-delimited file as:

"123 45th St., Apt. 18" (surrounded by quotes) This causes my database import process to error out.

The source file is comma-delimited. We have tried using a pipe | delimited file, but the same error still occurs. For reasons beyond my control, the client cannot feasibly save the source data as a tab-delimited file. Help?

The current procedu

- The client sends me the file as a comma-delimited .txt file in the format
"M","Smith ","Mary","J"," ","19591106"...etc for several dozen more columns
- I open the file in Excel via the Text Import Wizard, with a comma as the delimiter and all fields set to Text value
- I make the necessary edits
- I save the file as type Text (Tab Delimited)
- I then open the newly saved Tab Delimited file in Notepad to verify that everything is correct. All fields export correctly EXCEPT for those that contain a comma. They are export in quotes "texthere" as shown above.

How can I work around this problem? I have tried everything and can find no feasible solution.

Hello,

So, I tried opening an SQL file in Excel and it works fine. I am able to edit everything OK.

However, the problem comes when saving the file. If I save it in Tab Delimited format or even Unicode text format, it places a bunch of quotation marks all through the file rendering it useless. (I'm guessing that all the quotation marks render the SQL file useless, however I have not tried uploading it to my SQL db. Don't want to mess anything up on my website. )

So, is there a way to properly save a SQL file in Excel so that the saved SQL file is able to be used in an SQL db without any modification?

Thanks,
Dan

I am trying to created test data files. The data is currently in Excel and the widths of the columns have been set to that of the fields within a database. I want to save the Excel file so that the widths of the columns remain. I have tried saving as .prn and this works for some files however .prn is limited to 240 characters per line so any files which have more than this automatically wrap the columns. This is no good as when the file is read the columns are counted therefore the load gets confused.
I have tried saving as .csv and getting rid of the commas however this does not save the column widths.

How can I save the file so that the widths are saved to some sort of text file?
Other info - ultimately I need to change the file so that it is .dat and this is simple enough by changing the file extension.


Thanks in advance