Import CSV

[jsic1210]

Hello,

I'm trying to import a CSV. When I open the CSV, some of the values have decimal places (e.g. the dollar charge is $342.25). When the file is imported to a table, it shows up as $342.00. I import this file every month, and never have a problem. For some reason this month, the file is importing only the whole number. Has anyone seen this before?

Thanks!

 

[TJPoorman]

Double check your import specs and your table you are importing to. Verify the field in question is set as type Double and not Integer or Long Integer.

 

[CJ_London]

Sounds like you are using transfertext to make a table. If so, check the .csv file - if the first row of data contains a value without decimals Access will assume the value is not decimal or currency.

Better to have a template table to populate with your import which has the field types specified correctly.

Alternatively, sort your csv file so a value with decimals appears in the first line.

 

[jsic1210]

Thanks both for getting back to me so quickly.

@TJPoorman: Did you mean change the Field Size to Double? I tried that, but it didn't work.

@CJ_London: I sorted the file so a number with a decimal was on top. That worked, but I was hoping there was a better way to avoid it. I've looked at old files I've imported, and they didn't have decimals on top, but still downloaded properly.

Any other suggestions!

 

[CJ_London]

per my original post

Better to have a template table to populate with your import which has the field types specified correctly.

Or as TJ has said, check your import specs are correct

 

[jsic1210]

I actually do have a template table. My import goes to this template first, but it is not coming in to the template properly. I checked all the import specs, but can't find anything wrong. Here is my code:

Dim strMsg As String
Dim strSQL As String
Dim strAppend As String
Dim strDelete As String
Dim strDate As String
Dim strLoc As String
Dim strFile As String
Dim iMessage As String
Dim strMax As String

strLoc = "E:\MIS\DataFiles\CPU\"
strFile = strLoc & Format(Me.txtMonthEnding, "YYYYMM") & "_monthly_sbt.csv"

strDate = "#" & DateSerial(Year(Me.txtMonthEnding), Month(Me.txtMonthEnding), Day(Me.txtMonthEnding)) & "#"

strMsg = "Information already entered for " & Format(Me.txtMonthEnding, "MMMM YYYY") & " will be deleted"
strMsg = strMsg & " and replaced with imported CPU data."
strMsg = strMsg & vbCrLf & "Would you like to continue?"

'strSQL
strSQL = "UPDATE tblSBTMonthlyCPU SET [Month Ending Date] = " & strDate

'strAppend
strAppend = "INSERT INTO tblCPUMonthlyCharges (COID, [Company Name], [Net Excess Minutes], [Monthly Charge]," _
    & " [Month Ending Date])"
strAppend = strAppend & "SELECT tblSBTMonthlyCPU.COID, tblSBTMonthlyCPU.[Company Name], " _
    & " tblSBTMonthlyCPU.[Net Excess Minutes], tblSBTMonthlyCPU.['$$ Charge'], tblSBTMonthlyCPU.[Month Ending Date]"
strAppend = strAppend & " FROM tblSBTMonthlyCPU"

'strDelete
strDelete = "DELETE * FROM tblCPUMonthlyCharges"
strDelete = strDelete & " WHERE Month([Month Ending Date]) = " & Month(Me.txtMonthEnding)
strDelete = strDelete & " AND Year([Month Ending Date]) = " & Year(Me.txtMonthEnding)

On Error GoTo ImportErr

'Does data exist for this month
If DCount("ID", "tblCPUMonthlyCharges", "Month([Month Ending Date]) = " & Month(Me.txtMonthEnding) _
    & " And Year([Month Ending Date]) = " & Year(Me.txtMonthEnding)) > 0 Then
        If MsgBox(strMsg, vbYesNo, "Replace With New") = vbYes Then
            GoTo ImportUpdate
        End If
Else
    GoTo ImportUpdate
End If
    
ImportExit:
    DoCmd.SetWarnings True
    Exit Sub
    
ImportUpdate:
    'ImportMonthly_CPU_SBT
    DoCmd.SetWarnings False
    'Delete Old
        DoCmd.RunSQL "DELETE * FROM tblSBTMonthlyCPU"
    'Import New
        DoCmd.TransferText acImportDelim, , "tblSBTMonthlyCPU", strFile, True
    'Insert Date
        DoCmd.RunSQL strSQL
    'Delete any information from current month in tblCPUMonthlyCharges
        DoCmd.RunSQL strDelete
    'Append tblCPUMonthlyCharges
        DoCmd.RunSQL strAppend
        MsgBox "Update Completed Without Error"
    'Close
        DoCmd.Close acForm, "frmImportCPUInstructions2"
    GoTo ImportExit
    
ImportErr:
    MsgBox Error$
    GoTo ImportExit

 

[CJ_London]

I'm afraid that doesn't help - the problem will be as already described

Either your template table does not have the relevent field set to currency or double and/or your import spec is not set correctly and/or your source data has changed in some way - can you post a screenshot of your table design and also your import spec - on the screen which shows the types being imported - plus an example of the data being imported.

Personally I suspect the import spec

 

[jsic1210]

I'm having trouble inserting a screenshot. The attachment is too large. Here is what it looks like:

Field Size: Double
Format: Currency
Decimal Places: 2

Data being imported:

COID     Company Name     Net Excess Minutes     '$$ Charge'
12345    Company ABC                  1000          134.75

I still am not sure what you mean by import specs.

 

[CJ_London]

I still am not sure what you mean by import specs.

Sorry, I'm getting confused with importing excel.

Just a thought - you (or someone) didn't open the .csv file in excel and then save it again - as a .csv? Excel can change things, even if saved as a .csv file. I had that problem with a client who would open it in Excel to check it, then rather than just exiting, saved it again.

But going back to earlier posts, sorting it to provide a decimal figure at the top fixed the problem. So the only other thing I can suggest is try the following.

modify your template table so all fields are type text
run transfertext to populate the table
if all values are now correct (albiet text)
then modify your strAppend to convert from text to number/date as required - tho' most things will convert without any modification

 

[jsic1210]

Okay, I will try these. Thanks both for your help!