Export to CSV without rounding

[DreamGenius]

All

I've searched these fora and know about export specifications but I can't figure out how to make them work in my instances. The story so far:

I have a module which imports a file called DBImport.csv from the Desktop into a temporary table called DataTemp, after getting the user to confirm that:

1. Field names appear on the first line
2. The first field is called Member
3. Which of four unique identifiers are used under Member

Based on this information, the module then:

1. Adds a new column called Serial, at the end of the table
2. Populates Serial using one of four queries determined by 3 above
3. Exports the records where Serial is NULL to DBErrors.csv on the Desktop
4. Drops the column Member from DataTemp
5. Exports the records where Serial is NOT NULL to DBExport.csv on the Desktop
6. Drops DataTemp from the database

The challenge is that the export always rounds to 2 decimal places. I could get around this using an Export Specification, except that they aren't dynamic. This code copes with whatever data is thrown at it, so long as the criteria are met. However, rounding is unacceptable.

As an aside, if anyone can tell me how to move Serial to be the first column, that'd be appreciated too.

 

[KenHigg]

Here's a quick stab at it - Convert it to text first. ?

 

[DreamGenius]

I'm really trying to avoid that because the purpose of the module is to take anonymised scientific data and change the unique identifiers before running into another database elsewhere.

If the other machine then has to convert these back to numbers, it's another step that's potentially another point of failure. I prefer not to manipulate raw data unless absolutely necessary.

 

[gemma-the-husky]

instead of exporting a table, use a query based on the table - then yoo can

a) reorder the columns
b) almost certainly avoid the 2dp issue
c) pre-sort the extract

 

[DreamGenius]

instead of exporting a table, use a query based on the table.

Okay, I'll bite. The export is already based on a query rather than a table, to separate out the content of DBErrors.csv from DBExport.csv - making this slightly more detailed isn't a big deal but what's the best way of forcing the rounding, bearing in mind I'm already using a query?

 

[gemma-the-husky]

instead of exporting a table, use a query based on the table - then you can

a) reorder the columns
b) almost certainly avoid the 2dp issue
c) pre-sort the extract

 

[gemma-the-husky]

cant you right click a column, select format, and format to however many dps you want

make it general number, not fixed probably

i've just tried this to make sure - there's no problems with numbers being truncated - my nubers are going to excel with all the dps - it must be some setting in your table or query

 

[DreamGenius]

cant you right click a column, select format, and format to however many dps you want?

A point that I perhaps didn't make crystal clear in my OP is that the import file can have any number of fields with whatever names you like, so long as the first one is called Member and they're all unique so as not to cause the import to fail.

As a result, the export query is simply

SELECT * FROM DataTemp WHERE Serial IS NOT NULL

and I can't do any more with it than that, because I won't know in advance who many columns there are or what they might be called.

 

[DreamGenius]

I'm beginning to get the feeling that I need to be looking at extracting a recordset and writing it field by field. Right or wrong?

 

[KenHigg]

I'm really trying to avoid that because the purpose of the module is to take anonymised scientific data and change the unique identifiers before running into another database elsewhere.

If the other machine then has to convert these back to numbers, it's another step that's potentially another point of failure. I prefer not to manipulate raw data unless absolutely necessary.

I'm not 100% sure about this but I think when it goes into a .csv it's just character values anyway, in other words it's all text.

 

[gemma-the-husky]

which column(s) are being rounded

look at the table before it exported in access

does the data look correct in there?

if so then I dont see how a query just doing

"select * from tablename where seriial is not null orderby serial "

can introduce rounding issues?

-----------
incidentally, i dont think ken is right - you can set an export to surround text with "" characters (to avoid issues with embedded commas), but i dont think it turns numerics into text.


--------
perhaps the issue is the initial import

are you sure the csv is not truncted to 2 dps, before all your massage starts

 

[DreamGenius]

All numeric columns are being rounded, the data is good in the original import (checked in Excel and Notepad) and in the table before it's dropped.

It's definitely the export process because exporting the table does exactly the same thing.

 

[gemma-the-husky]

how are you exporting?

docmd.transfertext?
docmd.transferspreadsheet?

can you post the code?

 

[DreamGenius]

The relevant line of code is

DoCmd.TransferText acExportDelim, , "DataGood", Replace(strFilePath, "Import", "Export"), True

where strFilePath contains the full path and name of the imported file, DBImport.csv and just changes the output file name to DBExport.csv in the same relative location.

I've attached a ZIP file containing the database and the data files, if this helps. Unfortunately, I've had to delete the data from the tables - security. The CSV files contain bogus data anyway.

 

[KenHigg]

-----------
incidentally, i dont think ken is right - you can set an export to surround text with "" characters (to avoid issues with embedded commas), but i dont think it turns numerics into text.

The point was that it exports literal characters and qualifies text with quotes, which is what it does. Still won't fix his problem though...

 

[DJkarl]

I'm beginning to get the feeling that I need to be looking at extracting a recordset and writing it field by field. Right or wrong?

I think this would be the way to go. Here's a quick routine I wrote a while ago, I'm sure there are plenty of examples floating around.

Dim rs As DAO.Recordset
Dim exSTR As String, x As Long
Dim fHdl As Double
Set rs = CurrentDb.OpenRecordset("QueryOrTableName", dbOpenDynaset)
exSTR = ""
'Generate Headers
For x = 0 To rs.Fields.Count - 1
    exSTR = exSTR & Chr(34) & rs.Fields(x).Name & Chr(34) & ","
Next x
 
exSTR = Left(exSTR, Len(exSTR) - 1) 'Take off trailing Comma
exSTR = exSTR & vbCrLf 'Line Feed Carriage Return
Do Until rs.EOF
    For x = 0 To rs.Fields.Count - 1
        exSTR = exSTR & Chr(34) & rs.Fields(x) & Chr(34) & ","
    Next x
    exSTR = Left(exSTR, Len(exSTR) - 1)
    exSTR = exSTR & vbCrLf
rs.MoveNext
Loop
exSTR = Left(exSTR, Len(exSTR) - 2) 'Remove last CR and LF chars
fHdl = FreeFile
Open "C:\YourFile.csv" For Output As fHdl
    Print #fHdl, exSTR
Close fHdl
Set rs = Nothing

 

[DreamGenius]

DJKarl

Thank you for taking the time to write the code - it was easy to follow and worked a treat! I've no doubt I could have done it but it would have taken quite a while!

I've adapted it slightly, by making it a function that I could call with parameters, saving coding time. I've attached the code in a text file for those that might be interested.

Gemma

The original DoCmd.TransferText lines are still in there, commented out, so that you can see them in context. I appreciate your help in trying to make sure that I was doing the basic stuff correct. Unfortunately, I think this qualifies as an undocumented feature!

Regards and thanks

 

[gemma-the-husky]

so in your example "datagood" is an export specification?

is that coercing the field causing the problem to 2dps perchance?

 

[DreamGenius]

Gemma

No. DataGood and DataFail are two saved queries that reference the temporary table DataTemp. They create the mutually exclusive datasets of matched and unmatched data, using the SQL

SELECT * FROM DataTemp WHERE SerNo IS (NOT) NULL

There is nothing at all in the query that forces or implies rounding, at all.

 

[gemma-the-husky]

sorry, i missed the extra comma place holder in the transfer text line

i cant understand it - i dont have rounding issues exporting data in this way.