Excel vba csv разделитель
How does one open a semicolon delimited CSV file with VBA in Excel 2000?
Sample data
Code
In Excel 2003 11.8231.8221 SP3 with VBA 6.5.1025, I can open a semicolon delimited file with the following VBA code:
However, when the same code is run in Excel 2000 9.0.8961 SP1 with VBA 6.5.1025, I get the following error:
Compile error: Named argument not found
That is --I think-- because Excel 2000 doesn't know the named argument "Local".
Therefore, I deleted the "Local:=True" part. But the problem then is that an entire line from the CSV file is written into one cell instead of being split up into the separate semicolon delimited parts.
I have searched the Internet for a solution, but did not find anything useful and concise.
[Update 17.02.2009]
I tried the suggestion from user lc with the macro recorder. However, the results were confusing.
When I open the CSV file with menu File->Open. and then select the CSV file, the semicolon separated data is correctly parsed. And the recorded code is as simple as:
But when I use that VBA code in my macro, each line ends up in one cell again.
According to the suggestion from user barrowc, I also changed the The Windows "Regional and Language Options" settings from "German (Switzerland)" to "English (United States)". Even after restarting Excel, nothing changed, same problem.
I wonder why it is working on user Remou's system. What regional and language settings do you have?
8 Answers 8
[Update 22.02.2009]
In the meantime, I solved the problem by writing an import function myself instead of using Workbooks.OpenText.
I just open the CSV file as a text file, read line by line, split each line into the semicolon separated elements and write each element into a cell.
I still do not know why Workbooks.OpenText does not work on my system even though it seems to work on user Remou's system. I guess it might have something to do with the operating system language (English) and the regional and language settings (German, Switzerland), but I am not sure.
Anyway, the workaround works for me. Thank you all for you suggestions and help!
Not sure, but you can try recording a macro to do the same thing and check the VBA code it produces. You might get a hint there as to what's missing.
I find that this works for me in Excel 2000:
Here's the OpenText method from Excel 2000:
OpenText Method
Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.
Syntax
expression.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator)
source
and here's the Excel 2003 version:
OpenText Method [Excel 2003 VBA Language Reference]
Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.
expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)
source
so Local was indeed a new parameter for Excel 2003 and won't work in Excel 2000
No idea as to the cause of the erroneous behaviour. The Local parameter is defined as:
Local Optional Variant. Specify True if regional settings of the machine should be used for separators, numbers and data formatting.
You might want to double-check the regional settings on the Excel 2000 PC and check to see if there is anything which may cause the data to be wrongly interpreted. Also, try explicitly specifying the DecimalSeparator and ThousandsSeparator parameters on the Excel 2000 method and see if that helps
Я копирую данные в электронную таблицу, использую VBA для их форматирования, а затем сохраняю этот лист в файл CSV.
Я использую следующий код:
ws - это лист, который я сохранил.
Это дает мне CSV-файл с разделителями-запятыми.
Я хочу сохранить этот лист в файле, разделенном точками с запятой.
Я обнаружил следующее:
Я выполнил описанную выше процедуру и изменил свой код на:
Я по-прежнему получаю CSV-файл с разделителями-запятыми в качестве вывода.
Я использую Excel 2003, и моя ОС - Windows XP.
Большое спасибо за ваше предложение. Но я столкнулся с проблемой, когда попытался запустить ваш код. У меня есть какое-то значение, например, 1.0000000 в моей электронной таблице, и когда оно преобразуется в строку CStr(c.Value) , оно автоматически округляется до 1 . Не могли бы вы подсказать, как с этим бороться? Спасибо :)
Я только что проверил это, потому что у меня была такая же проблема. Имя файла в этом случае не работает.
Вот что сработало для меня:
В региональных настройках ->; с помощью Close вы должны использовать False .
Я использую Excel 2007 и только после вызова ".Close False" мой CSV был сохранен с использованием ";" как разделитель списка. Спасибо!
Не нужно объявлять все эти переменные, просто добавьте local: = true в конец вашего метода SaveAs, например:
Я столкнулся с той же проблемой, и после размышлений о попытке изменить «разделитель строк» в региональных настройках с использованием кода VBA и вызовов ядра я решил, что это будет намного сложнее, поэтому вместо этого я просто нашел несколько примеров использования Scripting.FileSystemObject чтобы вместо этого удовлетворить мои потребности.
Следующий код берет существующий файл csv и заменяет все запятые символом тильды «~».
Затем вы можете просто вызвать подпрограмму commaReplace из подпрограммы, которая создает файл csv.
Надеюсь, это кому-то поможет!
Для использования сценария vbs удалось выполнить следующую конструкцию:
.SaveAs Имя файла, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1
Последняя «1» в функции «SaveAs» равна Local = True.
Также точка с запятой должна быть определена как разделитель списков в региональных настройках ОС (см. Ответы выше).
Я пытаюсь настроить код Excel VBA, который открывает некоторые файлы .csv и разбивает на столбцы информацию, содержащуюся и ограниченную символом | . Мне удается открыть файл, но код, который я использую, открывает мои файлы без разделения текста по разделителю. Пока что я пробовал следующий код:
Я помню, какое-то время назад это свело меня с ума.
Кажется, что у Excel есть неконтролируемая жадность к .csv файлам. Если вы просто изменить концовку ( .txt , .dat или любой другой ), он будет работать!
Вы только что изменили часть строки «.csv» на «.txt» или создали новый текстовый файл, копирующий содержимое перед открытием?
Это не имеет никакого значения (вы можете испортить кодировку только при копировании содержимого в новый файл).
Exaclty. Единственный способ заставить Workbooks.OpenText работать с .csv с разделителями SemiColon - это использовать метод Name для временного изменения имени файла на .txt, загрузки содержимого и, наконец, переименования файла обратно в .csv.
Я пробовал это делать. Не работает. Но если вы попытаетесь сделать то же самое с текстовым файлом (скопировав и вставив содержимое csv в текстовый файл), это сработает.
Если вы посмотрите на ссылку MSDN , в описании параметра «Delimiter» метода «workbooks.open» конкретно говорится, что «если это текстовый файл» . Может это причина того, что он не работает.
Я не уверен. Для меня это тоже в новинку. Надеюсь это поможет.
Знак 124 - это труба "|"
Я думаю, это должно тебе помочь.
Решение Роуэна действительно работает. Ключ заключается в замене имени файла "Test.csv" в его решении на "Test.txt" в вашем местоположении "\ CSV_Files \". "Test.txt" не должен быть типом значения, разделенным запятыми. Это должен быть настоящий файл типа TXT.
Проверьте тип файла в проводнике Windows. Убедитесь, что это не CSV. Если вы используете тип CSV, вы фактически будете сообщать Excel, что данные анализируются запятой, а не разделителем канала.
Если ваша книга находится в корневом каталоге: c: \ Создайте каталог: C: \ CSV_Files Поместите текстовый файл: Test.txt в каталог \ CSV_Files
I'm currently use this function for saving, but I have a problem with it:
It automatically saves with , but I need it to save with ; in its file. Is it possible to change how it saves somehow?
I've tried googling around for this issue, but all macros regarding .csv file saving are just how to save in .csv and how to split multiple sheets to .csv .
Not easy I'm afraid. A csv is defined to have commas. And csv parsing in full generality is difficult as you have to handle quoted strings containing commas correctly. What I would do if I were you is post-process the csv yourself. In the simplest case you might get away with converting every comma in your output file with a semicolon. In more complex cases (as I've alluded to) you could use a regular expression. Or build your own saver in vba.
As it name stands CVS is Comma Seperatd Values. You could use a script to open the .csv file then replace ',' with ';' and save.
@Bathsheba Unfortunately, this is not true. Localized Versions of Excel use the ";" as Seperator. The german Version of Excel does this, I doesn't know which others do this. Also, csv can also stand for "character seperated values" - which makes csv a horribly broken format.
@Christian Sauer It's helpful to know that. Do you think saknar namn could somehow trick his Excel into thinking it's locale is, for example, German?
@Bathsheba: No, I think not. I searched for a easy way to do just that, because the localization of Functions is annoying, but I found no easy switch to do it.
8 Answers 8
Which language does your Excel use? If your native language uses ";" as default, you can pass the parameter "local:=True"
If not, your only choice is searching and replacing afterwards.
I had been looking this up to help resolve a similar issue I was having, I had an excel sheet that I export to a csv file, this is then uloaded elsewhere but requires the use of semicolons rather than commas for the character seperation, this worked fine when i was manually exporting the file as i had already changed the following Control Panel >> Region and Language >> additonal settings >> List separator from comma to semicolon. But when i tried to automate via VBA it defaulted back to comma, to fix I added the local paprameter as suggested by Christian Sauer above which then picks up the fact that i have changed my regional settings.
Thanks to Christian for the pointer.
Change Excel Options (Advanced, Editing options) to set Decimal separator to , (obviously (!))
I solved it adding "SaveChanges:=False" when closing workbook
It's quite possible this problem is not solvable using Excel VBA only. The problem is, while Excel Save As. uses machine locale define list separator value, Excel VBA always uses en-US locale, thus, it always uses , as a list separator.
I would recommend saving a CSV and then use custom console app/script for postprocessing. There is plenty of CSV parsers available which can read a ,-csv and then save it as ;-csv .
For people having issues with this code
The second line is really important, if wa.Close False is not there it will ask for approval to save, or if wa.Close True it will replace the ";" for ",".
After going into local settings and making ";" the list delimiter, VBA was still separating with a ",". Modified the code to the above and it was done.
Hope this throw some light for some
Great answers here, but they didn't work for me, because I'm trying to create a tool that will work for any user on their own computer, and I don't want to have to set up this locale stuff on everybody's PC. I even tried rolling my own CSV exporter using FileSystemObject, but the destination path name is on SharePoint, so that failed too.
Then I stumbled upon a very simple workaround: create a new worksheet that concatenates all your information into one column, separated by semicolons, e.g.:
Then add some code like this to export it:
Because it's all in one column, Excel won't add its own delimiters!
SOLUTION:
Concatenate all your information from multiple columns into one column and separate the data in the concatenated string by your separator of choice - i.e. the semicolon. Then copy the one column to a new file and save it.
LIMITATION:
However, beware that this solution is flawed when comma is present somewhere in the concatenated data as the concatenated string becomes wrapped by double quotes - see explanation below.
(1) When you save the file, VBA assumes that comma is the delimeter for the file. VBA also sees that comma is present in your string. Thus, in order to protect against future splitting of the string, VBA wraps the concatenated string in double quotes.
(2) You can try to bypass this problem by setting the system separator to semicolon and saving the file with the "Local:=True" parameter. However, now VBA knows that semicolon is the delimeter for the file. VBA also sees that semicolons are present in your string. Thus, in order to protect against future splitting of the string (VBA doesn't now that you actually want to split using the semicolons), VBA wraps the concatenated string in double quotes.
I'm trying to set up an Excel VBA code that opens up some .csv files and split into columns the information contained and delimited by the character | . I manage to open the file but the code I use opens my files without splitting the text according to the delimiter. So far I have tried the following code:
8 Answers 8
I remember this has driven me insane some time back.
It seems that Excel has an uncontrolled greed for .csv files. If you just change the ending ( .txt , .dat or whatever), it will work!
Did you just changed the ".csv" part of the string for ".txt" or do you created a new text file copying the content before open?
That would not make any difference (you might only screw up the encoding when you copy the content into a new file).
Exaclty. The only way I could get Workbooks.OpenText to work with a SemiColon delimited .csv was to use the Name method to temporarily change the filename to .txt, load the contents, then finally rename the file back to .csv.
I tried doing this. It doesn't work. But if you try doing the same on a text file(by copy pasting the csv contents to a text file), it works.
If you look at MSDN Link , it specifically says that 'if it is a text file' in the description of 'Delimiter' parameter of 'workbooks.open' method. Maybe this is the reason that it is not working.
I am not sure. This is a new thing for me too. Hope this helps.
Rowan's solution actually does work. The key is replace the file name "Test.csv" in his solution with "Test.txt" in your "\CSV_Files\" location. The "Test.txt" should not be a comma separate value type. It should be a true TXT file type.
Check the file type in Windows Explorer. Make sure it is not CSV. If you use a CSV type you will be in fact telling Excel the data is parsed by a comma rather than the pipe delimiter.
If your workbook is in root: c:\ Create the directory: C:\CSV_Files Put the text file: Test.txt in the directory \CSV_Files
Читайте также: