• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CSV file not opening proper in Excel

Vishal.jagani

New Member
Hello,
I have one CSV file when I try to open it in an excel It's not opening properly
If any body got solution let me know

When I tried to attach here Its not allowed to attach CSV file here so I can't attached as well


Thnks
Vishal
 
Hi Vishal ,

1. It is impossible to know what this means :
It's not opening properly

2. If this forum does not allow .csv files to be uploaded , just rename it to .txt and upload. As long as you mention that the file extension is in reality .csv , those who download it will rename it before opening it.

Narayan
 
Hi Narayan,

Here I have attached file
Let me know, I am trying to make a macro which first step is Auto open file (CSV format)

Thnks
Vishal
 

Attachments

  • ExampleFile.txt
    4.9 KB · Views: 0
The file imports ok?

upload_2015-4-21_15-2-9.png

Column B may need to be reformatted as a Number as it comes in by default using Scientific Formatted Numbers eg: 4.25.E+12
 
Hi Hui..

What process I am doing is

Data-> From Text -> Select File -> File Origin : Window (ANSI) and also tried with (1252: Western European (Windows))-> Next-> Delimiters : Semicolon - >Next - >Column data format : General -> Finish
 
I right clicked on the CSV File in windows and simply used Open with Excel
 
Yes It's Opening proper

But question is that how can I use this csv file through Macro ?

I mean is it any possibility to Auto Open file///?
 
Code:
  Workbooks.Open Filename:="C:\Users\xxx\yyy\ExampleFile.csv"
  Columns("B:B").NumberFormat = "0"
 
Hi Hui..

as per you mentioned, Its not opening proper pls see snapshot
I wrote
Workbooks.Open ("D:\Projects\xyz\abc\File.csv"), Columns("B:B").NumberFormat = "0"
Microsoft Excel - File.csv.png

How can we open comma separated or semicolon separated file through above line (coding line)
 
What happens when you Right Click on the File in Explorer and select Open?

I get this (Top 8 Rows)
upload_2015-4-21_22-58-17.png

Then I reformat Column B to get the bottom 8 rows
Columns C & D have lots more data in than shown
 
Hiya everyone, this is my first post!

Hiya Vishal, try this snippet from one of my modules:
Code:
sub VishalCsvInput()
    Dim dim1 As Integer, dim2 As Integer, fileNum As Integer
    Dim strCSV As String
    Dim fd As FileDialog
    Dim initArray As Variant, finArray As Variant, selectedFile As Variant
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fileNum = 1
    With fd
        If .Show = -1 Then
            For Each selectedFile In .SelectedItems
                strCSV = ImportTextFile(selectedFile)
                initArray = Split(strCSV, vbCrLf)
                dim1 = UBound(initArray)
                dim2 = UBound(Split(initArray(0), ","))
                ReDim finArray(dim1, dim2)
                For Idx1 = LBound(initArray) To UBound(initArray) - 1
                    For Idx2 = 0 To dim2
                        finArray(Idx1, Idx2) = Split(initArray(Idx1), ",")(Idx2)
                    Next
                Next
                Sheets("Sheet1").Range("A" & fileNum).Resize(UBound(finArray), UBound(Application.Transpose(finArray))) = finArray
                fileNum = fileNum + 100
            Next selectedFile
        End If
    End With
End sub

This is designed to work for multiple input files, but it should fit your purposes nicely.

Please let me know!

Kind regards, Stevie
 
Hi Stevie,

It seems you know very well Macro

Now let me tell you more,
I have one question : How can I make macro through coding by manually and also by recording

I really don't know much more about Macro but I want to learn more
If you have an answer regarding on above query let me know

What I want to make :
1- File will auto open (I think manually coding )
2- Once file wold open I want to record macro I mean I want to modify that data or I need to add some column or data

In step 1 Coding by manually because we can't open any file through recording right..?
In step 2 Once file open I can modification those data

Finally I could get my result in one sheet
 
Hello Hui..
As per you mentioned File is open fine but
I want auto open CSV file properly trough macro
I mean when I run macro It should popup for file location and after I could select that csv file and after I can change in this file through RECORDING MACRO
How's it possible
 
Hiya everyone, this is my first post!

Hiya Vishal, try this snippet from one of my modules:
Code:
sub VishalCsvInput()
    Dim dim1 As Integer, dim2 As Integer, fileNum As Integer
    Dim strCSV As String
    Dim fd As FileDialog
    Dim initArray As Variant, finArray As Variant, selectedFile As Variant
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fileNum = 1
    With fd
        If .Show = -1 Then
            For Each selectedFile In .SelectedItems
                strCSV = ImportTextFile(selectedFile)
                initArray = Split(strCSV, vbCrLf)
                dim1 = UBound(initArray)
                dim2 = UBound(Split(initArray(0), ","))
                ReDim finArray(dim1, dim2)
                For Idx1 = LBound(initArray) To UBound(initArray) - 1
                    For Idx2 = 0 To dim2
                        finArray(Idx1, Idx2) = Split(initArray(Idx1), ",")(Idx2)
                    Next
                Next
                Sheets("Sheet1").Range("A" & fileNum).Resize(UBound(finArray), UBound(Application.Transpose(finArray))) = finArray
                fileNum = fileNum + 100
            Next selectedFile
        End If
    End With
End sub

This is designed to work for multiple input files, but it should fit your purposes nicely.

Please let me know!

Kind regards, Stevie








Hey Steive

It's looking complicated code for me I don't have software skill so could you please make it some easy code
 
Opps! sorry, the code I posted before didn't include the ImportTextFile function code which is required for it to work. Please use the update below:

Code:
sub VishalCsvInput()
   Dim dim1 AsInteger, dim2 AsInteger, fileNum AsInteger
   Dim strCSV AsString
   Dim fd As FileDialog
   Dim initArray AsVariant, finArray AsVariant, selectedFile AsVariant
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fileNum = 1
   With fd
       If .Show = -1 Then
           ForEach selectedFile In .SelectedItems
                strCSV = ImportTextFile(selectedFile)
                initArray = Split(strCSV, vbCrLf)
                dim1 = UBound(initArray)
                dim2 = UBound(Split(initArray(0), ","))
               ReDim finArray(dim1, dim2)
               For Idx1 = LBound(initArray) ToUBound(initArray) - 1
                   For Idx2 = 0 To dim2
                        finArray(Idx1, Idx2) = Split(initArray(Idx1), ",")(Idx2)
                   Next
               Next
                Sheets("Sheet1").Range("A" & fileNum).Resize(UBound(finArray), UBound(Application.Transpose(finArray))) = finArray
                fileNum = fileNum + 100
           Next selectedFile
       EndIf
   EndWith
Endsub

Function ImportTextFile(strFile As Variant) As String
    Open strFile For Input As #1
    ImportTextfile = Input$(LOF(1), 1)
    Close #1
End Function

Vishal.jagani if you are having trouble following this code, then I would suggest looking into some other posts on this forum and teaching yourself the basics of VBA.
Google is your friend, and I would recommend www.cpearson.com aswell.
As far as solving your issue goes, simply copy that ^ code into a new module in your sheet and run it. Navigate to your csv file in the window that opens and click OK. Voila!

Kind regards,
Stevie
 

Hi Vishal !

Just with a single statement : Workbooks.OpenText (see VBA help) !

And maybe with parameter Local:=True of single statement
Workbooks.Open for a semi-colon delimiter in a non English Excel version …

Your last screenshot is not from joined text file !​
 
Last edited:
Hi Stevie

Your above code works fine.

I have a .CSV file with the following format.

TITOLO DOCUMENTO;LINE;COVER;INS;
;'ABC;'Aviation;'FIN;'005

I tweaked your code where it is "," to ";" and it worked like a charm. Is there any way we can deal with either "," and ";" seperator available in the CSV files.

Also, I am not sure why the data in the excel file after conversion is in "General" form instead the number, date are also in General form. is there any way to fix this as the type of the data should be the same it is like date should be date and txt should be txt.

Regards,
JD
 
Hi Jagdev,
I assume you want to be able to more easily select the delimiter, not use both simultaneously, as this could cause issues splitting where you don't want.
To do this, try:
Code:
sub VishalCsvInput()
   Dim dim1 AsInteger, dim2 AsInteger, fileNum AsInteger
   Dim delim as string
   Dim strCSV AsString
   Dim fd As FileDialog
   Dim initArray AsVariant, finArray AsVariant, selectedFile AsVariant
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   fileNum = 1
   delim = inputbox(Prompt:="Delimiter Selection", Title:= "Please enter your required delimiter", Default:=",")
   With fd
       If .Show = -1 Then
           ForEach selectedFile In .SelectedItems
                strCSV = ImportTextFile(selectedFile)
                initArray = Split(strCSV, vbCrLf)
                dim1 = UBound(initArray)
                dim2 = UBound(Split(initArray(0), delim))
               ReDim finArray(dim1, dim2)
               For Idx1 = LBound(initArray) ToUBound(initArray) - 1
                   For Idx2 = 0 To dim2
                        finArray(Idx1, Idx2) = Split(initArray(Idx1), delim)(Idx2)
                   Next
               Next
                Sheets("Sheet1").Range("A" & fileNum).Resize(UBound(finArray), UBound(Application.Transpose(finArray))) = finArray
                fileNum = fileNum + 100
           Next selectedFile
       EndIf
   EndWith
Endsub

Function ImportTextFile(strFile AsVariant) AsString
   Open strFile ForInputAs #1
    ImportTextfile = Input$(LOF(1), 1)
   Close #1
EndFunction

This will ask you for your delimiter when used, and you can type it in.

For the 'General' issue, you are pasting CSV data into an un-formatted range. The data itself does not retain formatting. You can either format the range beforehand, afterwards, or add some code to format the ranges automatically, although this will be complex as you will have to detect things like dates.

Hope this helps
 
Last edited:
Hi Stevie

A small doubt. Is it possible to remove special charactor " ' " from the CSV file and then perform the conversion operation on it.

Regards,
JD
 
Hi Jagdev,
I'm not set up to test, but try this:

Code:
Sub VishalCsvInput()
   Dim dim1 As Integer, dim2 As Integer, fileNum As Integer
   Dim delim As String, element As String
   Dim strCSV As String
   Dim fd As FileDialog
   Dim initArray As Variant, finArray As Variant, selectedFile As Variant
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   fileNum = 1
   delim = InputBox(Prompt:="Delimiter Selection", Title:="Please enter your required delimiter", Default:=",")
   With fd
       If .Show = -1 Then
           For Each selectedFile In .SelectedItems
                strCSV = ImportTextFile(selectedFile)
                initArray = Split(strCSV, vbCrLf)
                dim1 = UBound(initArray)
                dim2 = UBound(Split(initArray(0), delim))
               ReDim finArray(dim1, dim2)
               For Idx1 = LBound(initArray) To UBound(initArray) - 1
                   For Idx2 = 0 To dim2
                        element = Split(initArray(Idx1), delim)(Idx2)
                        finArray(Idx1, Idx2) = Replace(element, "'", "")
                   Next
               Next
                Sheets("Sheet1").Range("A" & fileNum).Resize(UBound(finArray), UBound(Application.Transpose(finArray))) = finArray
                fileNum = fileNum + 100
           Next selectedFile
       End If
   End With
End Sub

Function ImportTextFile(strFile As Variant) As String
   Open strFile For Input As #1
    ImportTextFile = Input$(LOF(1), 1)
   Close #1
End Function

This trims out "'" after each part has been split down.
 
Last edited by a moderator:
Back
Top