How to open CSV file in EXCEL (Sharing Begging)

[Debraj]

Hi friends..

We received CSV file from EU country.


I dont know why, but they use ( as separator (why?)

So technically this CSV file is Semicolon Separated Value (SSV) file,


MAC Excel and OpenOffice Excel always read it properly, but Window Excel fail to read it (why?)


* Text to column wizard sometime Fail .. as address line also contain (

* OpenWIth > Notepad .. Find (";") (with quote) and ReplaceWith (",")(with quote) >> sometime Fail.. as only 37MB CSV file (with minimum 187654 X 134 matrix) in Notepad is only editable after a tea break..

* OpenWith > Notepad .. at the first line write

 sep=; ... is also force me to take a nap..

* I can read the file with

[code]Open FileName For Input As #1

Split(String,delimeter,...)

Workbook.Open Filename, [Delimiter]:=";"[/code]

.. and many other way.. but I am not satisfied..


* Like Application.PathSeparator or decmimal/thousand separator, is there any option to set DefaultSeparator or DigitGroupingSeparator.


Still begging to you for some smart way.. as I have a huge level of expectation from this site..


Any suggestions are welcome..

 

[bobhc]

Good day Debraj Roy "I dont know why, but they use ( as separator (why?)" because that's the way we do, but we also have respect for others who do it all wrong


Have you tried copying the file and saving, it should save as per your local settings, even if they are all wrong

 

[SirJB7]

Hi, Debraj Roy!


Just running out of time, so here goes a blind shot in the dark.


The three most used systems (PC Windows, Mac iOS and Linux) use different ways for separating lines. I faced a similar issue each time I had to export a text file (no matter the extension, if .txt, .csv, just the contents and the specifically the separators), so I made a converter and forgot the details.


Windows: I'm sure it uses CR+LF -Chr$(13)+Chr$(10)-, 2 chars as separator

iOS: CR -Chr$(13)-, 1 char as separator

Linux: LF -Chr$(10)-, 1 char as separator


May be the last two are swapped, I don't remember exactly now, but it this results to be the issue you can try both combinations.


Regards!

 

[Debraj]

@bobhc..

sorry for being rude.. I dont have any complain of using PathSeparator or Thousand/Decimal separator.. We all are managing all the situation, just like right now its 2:30 past midnight in my system..and you have just finish your lunch..

B(ut).. in case of Globalization, Window or Office must have any option to adjust it..

I am begging for this one only..

 

[SirJB7]

Hi, Debraj Roy!

In the rush I didn't read accurately what you were asking for. I apologize, I read separator and quickly and wrongly associated it with record separator.

Regards!

 

[bobhc]

Debraj Roy I did not take your post as being rude just took it as an opportunity to hopefully raise a smile I have done as you are trying to do, I saved a file that was not in the settings of the local machine, saving it changed as required.

 

[SirJB7]

Hi, Debraj Roy!


Maybe you find this interesting, try copying and pasting what follows in the immediate window pane and give a look at it:

-----

Print Application.International(xlAlternateArraySeparator); Application.International(xlColumnSeparator); Application.International(xlDecimalSeparator); Application.International(xlListSeparator); Application.International(xlRowSeparator); Application.International(xlThousandsSeparator)

-----


But they're read only properties, and I don't know how useful would they be for your conversion purposes as the can't be set, just read. Maybe for automatizing a macro for opening files indicating what type of separator it has and convert it to the proper one.


About changing Decimal and Thousands separators here's the built-in help Excel code example:

-----

[pre]

Sub ChangeSystemSeparators()

Range("A1").Formula = "1,234,567.89"
MsgBox "The system separators will now change."

' Define separators and apply.
Application.DecimalSeparator = "-"
Application.ThousandsSeparator = "-"
Application.UseSystemSeparators = False

End Sub

[/pre]
-----


BTW, Application.PathSeparator is read only too.


Regards!

 

[shrivallabha]

Similar thread had come up on VBAX. Look at Kenneth Hobson's and snb's posts.

http://www.vbaexpress.com/forum/showthread.php?t=42769