Can you edit a Schema file in VBA?

[largeselection]

So I have a connection which is created in VBA to a text file that I have saved somewhere. I just found that there are a bunch of rows which have 0s for a certain column, but have decimals for the majority of the rest of the rows (0.5,0.6,etc), but when it creates the connection in VBA, the max rows it scans are 8 (I set that to keep it quick), so it thinks that column is "integers" so everything comes back as 0s. I need that to come back as Float so that I can get the decimals, but the only way I know how to do that is to create a Schema.ini file so that when it imports, it does it according to the schema. Is it possible to do this in vba directly? Either something that would tell excel that the schema for that entry = float or something that will open or create a schema file and adjust it so that it changes Integer to Float?

Sorry if I'm not being clear, please ask any questions you may have and thanks for your insights!

 

[largeselection]

Or is it possible to create a schema in word and save it as a schema.ini file?

 

[xenou]

I've never used a schema.ini file for this purpose (issues with decimal values) but assuming that it would solve your problem you can easily use notepad or any text editor to create one. You can write to text files with vba also.

 

[largeselection]

Hi thanks for looking. I don't usually need to use this schema either but I'm running into an issue when I try and import because it automatically thinks one of my columns is integer when it needs to be float. I know how to edit the schema file it's just annoying because the file name is going to be changing regularly so it will be a pain to have to remember to alter that text file in notepad all the time. Thanks for your suggestion!

 

[xenou]

How do you import the file? How large is it? What kind of data is in the file?

 

[largeselection]

Well I actually don't import the file. I just create an external connection to a csv file so I can manipulate some pivot tables. The csv file can be anywhere between 300,000 and 800,000 rows and about 70 columns. File size is around 200mb for a 300,000 row file. The data is a mix of text columns integer columns and currency (float) columns.

 

[xenou]

what about renaming the file instead of re-writing the schema? Don't know if you have any control over the source file but possibly using 0.00 for zeros (rather than 0) will prompt Excel to use floats.

 

[largeselection]

I was thinking about if 0.00 might work. How would I change that and get it to stick? I create the csv file actually so if I changed it to currency so that in the file it showed 0.00 when I save as to a csv file would it keep? Or would it automatically change it to 0?

 

[xenou]

My experience with saving files to csv is that they follow the format you have. Proof is in the puddin' ... I guess it depends on how you create the file!