convert csv to xls

[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.

 

[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.

 

[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.