none
How to Import CSV Files to Excel 2007 using VBA with more than 255 Columns ?

    Question

  • Hi friends, 

     

    I am trying to Export report from Reporting service into Excel which has more than 255 Columns and Reporting Service does not support .Xlsx (Excel 2007/2010).

    Now I need to Export to CSV and Import to Excel 2007 (.Xlsx) . I used VBA script to import CSV but still giving me issue with 255 Cols.

    Is there any way I can use CSV import to Excel 2007\2010 not manually , one button click. 

    here is my code

     

    Public Sub ImportTextFile(Fname As String, Sep As String)
    
      Dim RowNdx As Long
      Dim ColNdx As Integer
      Dim TempVal As Variant
      Dim WholeLine As String
      Dim Pos As Integer
      Dim NextPos As Integer
      Dim SaveColNdx As Integer
      
      Application.ScreenUpdating = False
      'On Error GoTo EndMacro:
      
      SaveColNdx = ActiveCell.Column
      RowNdx = ActiveCell.Row
      
      Open Fname For Input Access Read As #1
      
      
      While Not EOF(1)
        Line Input #1, WholeLine
        If Right(WholeLine, 1) <> Sep Then
          WholeLine = WholeLine & Sep
        End If
        ColNdx = SaveColNdx
        Pos = 1
        NextPos = InStr(Pos, WholeLine, Sep)
        While NextPos >= 1
          TempVal = Mid(WholeLine, Pos, NextPos - Pos)
          Cells(RowNdx, ColNdx).Value = TempVal
          Pos = NextPos + 1
          ColNdx = ColNdx + 1
          NextPos = InStr(Pos, WholeLine, Sep)
        Wend
        RowNdx = RowNdx + 1
      Wend
    End Sub
    


    Tej http://tejzatms.blogspot.com/
    Friday, December 17, 2010 9:15 PM

Answers

  • To Tejz, 

    Your comment: "requirement is not any manual step. application allows to on click solution.  thats why I am trying to import line by line."

    The method I gave you will not be a manual system after implementation; only the initial import and recording of the VBA code is manual as is writing any code. Once you have recorded the code during a manual import, you can perform any editing it may require and then assign the code to a button so the code will perform the import.

    I tested importing data with this method using 350 columns and it worked fine. After you have it in an Excel worksheet, you can then process the data one line at a time similar to the code you already have except you are getting the data row by row from a worksheet instead of line by line from a csv file.


    Regards, OssieMac
    • Proposed as answer by Bruce Song Thursday, December 23, 2010 11:05 AM
    • Marked as answer by Bruce Song Friday, December 24, 2010 9:41 AM
    Tuesday, December 21, 2010 8:03 PM

All replies

  • Instead of importing line by line and processing each line as imported, try importing the entire csv file as is into a clean Excel worksheet and then perform the processing required. Note you need a clean worksheet to do this. You can record the required code with the following method. This method is not simply opening a csv file but importing it to a clean worksheet.

    Select Data Ribbon

    In the 'Get External Data' block (Far left of ribbon) select 'From text'

    Navigate to and select the csv file and click Import.

    The Text Import wizard will open. Follow the prompts through the wizard, setting the delimeters to comma and then you can set the type of data for each column if required. Note when setting a column to date, tell the system what type date format exists is in the text file; not the date format you want to finish up with because Excel will look after converting date to your regional date format. Only set the column format for any columns that cannot be left as general.

    You can then process the imported data row by row instead of processing each row as it is imported.


    Regards, OssieMac
    Saturday, December 18, 2010 7:39 AM
  • what is the issue with the code i check it is working fine ?

    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Rajender Saini Aditi Enterprise Solutions – Partnering Innovation | www.aditi.com
    Saturday, December 18, 2010 9:48 AM
  • other way to do it is

                        Open TargetFolder & fn For Input As #1
                        lngCtr = 0
                        Do
                            Line Input #1, varData
                            lngCtr = lngCtr + 1
                            Range("A" & lngCtr).TextToColumns Destination:=Range("A" & lngCtr), DataType:=xlDelimited, _
                                                              TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                                              Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                         Loop While EOF(1) = False                                                                                               :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 1), Array(6, 4), Array(7, 4), _
                                                                                                                                Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True
                        Close #1

    See the documentation of Range("A" & lngCtr).TextToColumn


    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Rajender Saini Aditi Enterprise Solutions – Partnering Innovation | www.aditi.com
    Saturday, December 18, 2010 10:39 AM
  • Try
    http://www.rondebruin.nl/merge.htm

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm

    "Tejz" wrote in message news:a446a507-2d5a-4807-8c86-6b577f72fcc2@communitybridge.codeplex.com...

    Hi friends,

    I am trying to Export report from Reporting service into Excel which has more than 255 Columns and Reporting Service does not support .Xlsx (Excel 2007/2010).

    Now I need to Export to CSV and Import to Excel 2007 (.Xlsx) . I used VBA script to import CSV but still giving me issue with 255 Cols.

    Is there any way I can use CSV import to Excel 2007\2010 not manually , one button click.

    here is my code

    Public Sub ImportTextFile(Fname As String, Sep As String)
      Dim RowNdx As Long
     Dim ColNdx As Integer
     Dim TempVal As Variant
     Dim WholeLine As String
     Dim Pos As Integer
     Dim NextPos As Integer
     Dim SaveColNdx As Integer
      Application.ScreenUpdating = False
     'On Error GoTo EndMacro:
      SaveColNdx = ActiveCell.Column
     RowNdx = ActiveCell.Row
      Open Fname For Input Access Read As #1
      While Not EOF(1)
       Line Input #1, WholeLine
       If Right(WholeLine, 1) <> Sep Then
         WholeLine = WholeLine & Sep
       End If
       ColNdx = SaveColNdx
       Pos = 1
       NextPos = InStr(Pos, WholeLine, Sep)
       While NextPos >= 1
         TempVal = Mid(WholeLine, Pos, NextPos - Pos)
         Cells(RowNdx, ColNdx).Value = TempVal
         Pos = NextPos + 1
         ColNdx = ColNdx + 1
         NextPos = InStr(Pos, WholeLine, Sep)
       Wend
       RowNdx = RowNdx + 1
     Wend
    End Sub
    

    Tej http://tejzatms.blogspot.com/

    Saturday, December 18, 2010 5:36 PM
  • To, OssieMac

    requirement is not any manual step. application allows to on click solution.  thats why I am trying to import line by line.

     

    Thanks,


    Tej http://tejzatms.blogspot.com/
    Tuesday, December 21, 2010 3:11 PM
  • Rajen,

    Issue is I have more than 255 columns in CSV file, when I am trying to import data with above macro, it fails @ 

    Cells(RowNdx, ColNdx).Value = TempVal 

    when reach to ColNdx=256 column.

    because default macro support excel 2003 which support 255 columns. I dont have any idea how to change macro to support excel2007/2010.

     

    Thanks


    Tej http://tejzatms.blogspot.com/
    Tuesday, December 21, 2010 3:14 PM
  • T,o Ron De

    Thanks for you solution  but It doesn't help me. I could not select .CSV file. and my aim is different than merging files. Excel2007 is allows more than 255 cols , I need a way to use excel feature using macro, 

     

    Thanks


    Tej http://tejzatms.blogspot.com/
    Tuesday, December 21, 2010 3:24 PM
  • To Tejz, 

    Your comment: "requirement is not any manual step. application allows to on click solution.  thats why I am trying to import line by line."

    The method I gave you will not be a manual system after implementation; only the initial import and recording of the VBA code is manual as is writing any code. Once you have recorded the code during a manual import, you can perform any editing it may require and then assign the code to a button so the code will perform the import.

    I tested importing data with this method using 350 columns and it worked fine. After you have it in an Excel worksheet, you can then process the data one line at a time similar to the code you already have except you are getting the data row by row from a worksheet instead of line by line from a csv file.


    Regards, OssieMac
    • Proposed as answer by Bruce Song Thursday, December 23, 2010 11:05 AM
    • Marked as answer by Bruce Song Friday, December 24, 2010 9:41 AM
    Tuesday, December 21, 2010 8:03 PM