Rounding numbers when I import

[ckeener]

I am trying to import a comma delimited text file,

ex.

101,2005,194,1626,1029,0,0,88.3,64.28,97.8,88.3,74.7,14.22,18.54,216.3,14.22,230.1,30.39

but when I import it, the decimals are all removed. I tried setting the decimal places manually and setting an input mask but it still does not give the correct decimal.
For example, 30.39, when it is imported to field set to 2 decimal places and with an input mask of ###.00, converts to 30.00 in my table.

Any ideas on what I should change to correct this?

Thanks

 

[lespaul]

do you have a table pre-defined that you are importing into? What is the data type of the field this information is going into?

If you are using a MakeTable query, then the issue is probably the same thing that happens with transfers from Excel. Access is looking at the first record which contains '101' which is an integer, where as 88.3 would be set as a float or a double. So, when the import routine gets to the doubles, it treats them as integers because that's what the first record is.

The solution is to have a pre-defined table that you import into with the fields correctly defined prior to the import.

HTH

Leslie

 

[ckeener]

Thanks for your reply Leslie

Right, that is what is confusing me. I have a pre-defined table in which each of the fields are configured. I have set the fields to be number data-types, set the decimal manually to whatever would be correct based on the text file, and set an input mask to force numbers after each decimal.

I am using code and a saved import spec to import this

Private Sub cmdImport_Click()
Set db = CurrentDb()
    Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
    With dlgOpen
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                    rtn = EditWeather(vrtSelectedItem) 
                    If MsgBox("Add " & vrtSelectedItem & " to the database?", vbExclamation + vbYesNo) = vbYes Then
                        DoCmd.TransferText acImportDelim, "Weather_Import_Spec", "tblWeather", _
                            vrtSelectedItem, True
                    End If
            Next vrtSelectedItem
        Else
        End If
    End With
    Set dlgOpen = Nothing
    db.Close
End Sub

would that cause a problem?

Thanks

 

[SkipVought]

???

"...and set an input mask to force numbers after each decimal"

Do the values that you are importing, already have decimal places (digits to the right of the decimal)?

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[ckeener]

Yes Skip,

The numbers are weather related and several of them do have decimals.

for example:
Text Doc Input Mask Table Value
Temperature 88.3 ###.0 88.0
BarometerInchs 30.39 ###.00 30.00

I set the Input Mask in the tblWeather's design view for each field.

Thanks

 

[RoyVidar]

What field size are these fields? They should probably be single or double (input mask is for user input)

Roy-Vidar

 

[ckeener]

Hi Roy,

They are the default Long Integer. I changed it to a Double but there was no difference in the results.

I guess I misunderstood the input mask reason.

Thanks

 

[lespaul]

They should probably be single or double

contains '101' which is an integer, where as 88.3 would be set as a float or a double.

I suggested looking at that first response?

I changed it to a Double but there was no difference in the results.

Are you sure? Double field type should definitely take a decimal.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[glickster]

cKeener,

Have you solved this problem?

I have exactly the same problem and I'm stumped. If I import the CSV file into a new table using the manual set up method (Get External Data...) my decimals remain intact. But when I use DoCmd.TransferText they always round off. Leave it to Microsoft to make something so simple so difficult! It appears TransferText uses a different DLL than "Get External Data..." for importing data.

G

 

[ckeener]

Hi glickster,

It is now working and I think I was mistaken in saying that the double did not work.

When you say that you are importing to a new table do you mean that the table does not exist until you import? Or does the table already exist for you to be able to edit the field properties for the table?

ckeener

 

[glickster]

I'm creating a new table on the fly instead of importing to an existing table. I guess I could try a manual import into an existing table, but that won't solve my problem. I need to use DoCmd.TransferText because I'm using VBA to import multiple data (CSV) files.

I after I read your posts, I dropped my target table and recreated it using Double type just to make sure the MDB wasn't "holding on" to the original table schema somewhere (my original table had Long Integer not realizing that the Hours fields could have fractions).

I'd post to MSDN but the Navy in it's infinite wisdom to prevent access to personal webmail accounts (Hotmail) has shut off access to MS Passport. I'll have to post tonight.

Thanks,

G