Importing CSV and auto-formating.

[mypeke]

How can I setup in Excel so that when I import a CSV file it will automatically handle the following:
1) Preserve any preceding zeros in the fields.
2) Auto-format the fields/rows to defined fonts and field sizes.

Our application has a csv extract ability, but I'm having to do a lot of formatting to make it "nice and viewable".

I apologize if this is a simple question, but I've had to teach myself Excel. If is a webpage that I can be forwarded to that is fine.

Thanks
Caleb.

 

[Fat Cat]

You could do it by using the Open File Wizard and setting the columns that contain leading zeros to text rather than a general format. You would then need to manually change the fonts etc once imported.

Depending on your VBA macro ability, the following could give you ideas on how to automatically import and format data. It runs off a CommandButton placed on the spreadsheet.

Use the menu VIEW - TOOLBARS - CONTROL BOX and then put a COMMANDBOX onto your sheet.

Then double click on the command button to go into the VBA editor and paste in the code below.

go back to your sheet and click on the DESIGN MODE toggle icon on the control toolbox (its the top left one with a little set square).

Click on the command button and see what happens.

Dim oldText As String
Dim rowCount As Long
Dim colCount As Long
Dim allData As Variant
Dim cellFormat As String

rowCount = 1                                        'set to row you want imported data to start from
colCount = 1                                        'set to col you want imported data to start from

fName = Application.GetOpenFilename                 'find your file
Open fName For Input As #1                          'open it
While Not EOF(1)                                    'loop till end of the file
    Line Input #1, oldText                          'read in a line of text
    allData = Split(oldText, ",")                   'split into bits based on the comma (,) positions
    
    For i = 0 To UBound(allData, 1)                 'work out how many bits of data you have
        Select Case Left(allData(i), 1)             'check if the leading chr is 0
        Case "0"                                    'check if the leading chr is 0
            If Mid(allData(i), 2, 1) = "." Then     'if a decimal number leave format as general
                With Cells(rowCount, colCount + i)
                    .NumberFormat = "General"
                End With
            Else                                    'if not a decimal number set format to Text
                With Cells(rowCount, colCount + i)
                    .NumberFormat = "@"
                    .Font.ColorIndex = 5            'give it a different colour
                End With
            End If
        Case Chr(34)                                'check for any " chrs around text and strip out
            allData(i) = Mid(allData(i), 2, Len(allData(i)) - 2)
            With Cells(rowCount, colCount + i)      'change the font style for text
                .NumberFormat = "@"
                .Font.Name = "Antique Olive Roman"
                .Font.FontStyle = "Italic"
                .Font.Size = 10
                .Font.Strikethrough = False
                .Font.Superscript = False
                .Font.Subscript = False
                .Font.OutlineFont = False
                .Font.Shadow = False
                .Font.Underline = xlUnderlineStyleNone
                .Font.ColorIndex = 3
            End With
        End Select
        Cells(rowCount, colCount + i).Value = allData(i)    'copy new data to sheet
    Next i
    rowCount = rowCount + 1                         'increment row counter
Wend
Close #1                                            'don't forget to close the file