convert csv to xls (1 Viewer)

[datacontrol]

csv file

csv file

 

[Fornatian]

try this code:

Public Sub ConvertCSVtoXL(strCSVPath As String)

Dim appExcel As Excel.Application

'Switch to Microsoft Excel so it won't go away when you finish.
    On Error Resume Next
    AppActivate "Microsoft Excel"
    
    'If Excel isn't running, start and activate it
    If Err Then
    Shell "c:\Program Files\Microsoft Office\Office\" _
        & "Excel /Automation", vbHide
    AppActivate "Microsoft Excel"
    End If
    On Error GoTo 0
    
    'Get an Application object so you can automate Excel.
    Set appExcel = GetObject(, "Excel.Application")

    With appExcel
    
    .Workbooks.Open FileName:=strCSVPath
    .Cells.EntireColumn.AutoFit
    .Columns("A:A").NumberFormat = "0"
    
    
Dim i As Byte
Dim i2 As Integer
Dim aYear As Integer
Dim aMonth As Byte
Dim aDay As Byte

For i = 4 To 5
For i2 = 3 To .Cells(3, i).End(xlDown).Row

aYear = Left(.Cells(i2, i), 4)
aMonth = Mid(.Cells(i2, i), 5, 2)
aDay = Right(.Cells(i2, i), 2)

.Cells(i2, i) = DateSerial(aYear, aMonth, aDay)
Next i2
Next i
    
    .ActiveWorkbook.SaveAs FileName:=Left(strCSVPath, Len(strCSVPath) - 3) & "xls" _
        , FileFormat:=xlNormal
    
    End With
    
    appExcel.Quit
Set appExcel = Nothing
    
    MsgBox "File '" & strCSVPath & "' has been converted to excel under the same " & _
    "filename with an XLS extension"

End Sub

 

[datacontrol]

okay....

This code works really well, thanks.

I still have a problem importing the generated .xls file into Access. The first column is the trouble some one. when I import, I lose 73 pieces of data from this column, because of field size and the fact that the data is alphanumeric in some cases.

The method I am using to import the .xls is transfer spreadsheet via a macro. I have even tried creating the "dump" table myself, specifying text, long integer, etc. nothing seems to work. It is just this field that is causing the issues.

I attached the generated error table upon import.

line 48: BRG10000443464

- this is the first error line (notice it contains ALPHA)

 

[datacontrol]

error table

 

[pdx_man]

When doing an import from Excel, Access is going to 'guess' what the data type should be based on the first piece of data after the row headers. It will guess wrong if the rest of the data in not of the same data type. What I always do is to either ensure that the first row of data contains data typical of what is to follow, or, I put in a dummy row that I delete later where I use:
A: for alphanumeric
9.9 for numbers

 

[datacontrol]

Thanks

That makes sense, I have run across this issue before. I suppose I could use some code when converting the csv file to xls to append a row of data as a "key" at the top?

Then, use a query that doesn't select the first row for the table content?

If anyone can suggest a better way, let me know. I am unfamiliar with the code that would be required in order to create a dummy row on the fly.

 

[datacontrol]

Any help? Please, i have a deadline on this project.

Thanks!

 

[datacontrol]

dummy data

What code should I use to insert a dummy row of data when the csv file is converted to xls?

 

[datacontrol]

problems in code

Fornation,
The code that you provided is troublesome in two ways. For one, apparently the csv and excel files are not being properly "shut down" after the code executes. If I try to delete thes files, I get a sharing violation. Also, I get a data type mis-match on this line:

aYear = Left(.Cells(i2, i), 4)

My file is still in the same format, although I hade made some changes to my interface. May I email you a zipped mdb file? I have a meeting on this interface Thursday.

Thanks.

 

[Pat Hartman]

I reread the post and can't figure out why you are doing this extra step rather than just importing the .csv file directly into Access. Unlike a spreadsheet, when you import a .csv file, you can create an import spec to control certain aspects of the import such as defining a field as text. Once you save the import spec, the import is repeatable in code by simply referencing the import spec in the TransferText Method.

To create the import spec, you need to go through the import wizard manually ONCE. Just make sure to press the Advanced button before the wizard ends so you get to the option to save the spec. Test it using a macro and if it works, implement the import in your code using the TransferText Method.

 

[datacontrol]

PROBLEM

I am familiar with creating import specs, they are a real life saver. The reason that I was converting to excel first is because the first line or row of my csv file contains consistent erroneous data. The field headings appear on row 2. I have no idea how to manipulate the csv file via vba, to delete the 1st row. If I can do this, then importing the csv via spec is a valid option. Any ideas?

Thanks.

 

[Pat Hartman]

If you search the archives, you will find posts that tell you how to import a file via VBA. An alternative is to create a table that you can use to import into. Then use an append query to copy the data from the dummy import table and append it to the real table, bypassing the first two rows.

 

[Duncman]

Excel viewer

I have the MS Excel Viewer which has no "importing" capabilities. It will only open Excel files (xls xlm etc) and I need to open a .csv file with it. Any shareware or freeware converter out there you'ld recommend? I haven't found much via Googling. Thx

 

[mvraditya]

I think you guys are thinking too much for a trivial issue.

open the file in excel
Initially, Select the First column

1.Goto Data->Text to columns
2.Select the option "Delimited"
3.Select the required Delimiters(For ex,Check the "Semicolon" option)

 

[sulekha]

if you want to instantly convert csv file to xls then use this Excel Converter : aspose[dot]com/java/excel-component.aspx

its a cloud API so your file will be secured also if you use this API.