Removing leading positive symbol during text file import (1 Viewer)

Gavx · Feb 22, 2018

I am trying to import a CSV file listing transactions in my bank account.
An example of a record is;

dd/mm/yyyy, "+##.##", "transaction description"

The number field could be preceded by a + or a -.

The import works perfectly with the values preceded by a negative, however, the values preceded with a positive symbol are skipped.

How do I fix this?

Thanks for any help,
Gavin

The_Doc_Man · Feb 22, 2018

The action of removing the leading plus-sign means that column is being imported as though it is numeric, even though you show it as being inside quotes. The fact of the source file being in .CSV format doesn't stop that conversion from happening.

If you want to retain the leading "+" then the column has to be interpreted as text end-to-end, which would only occur if the targeted field (in the targeted table) is also text, not numeric. If that field is decimal, single, double, or currency, it is not text.

One other possibility exists, and that would require creating an saving an import specification that explicitly declares that column as text.

You can search this forum for the subject of "Import Specifications" because we have had many articles on the subject. Therefore I will avoid re-inventing that wheel.

Pat Hartman · Feb 22, 2018

Rather than importing directly to a table, I always link external files and use append queries to get the data into my permanent tables. That lets me solve problems like this. In your query, you would strip the +. Using the Replace() function

Select TranDate, Replace(TranAmt, "+", Null) As Exp1, ....

Once the select query works, convert it to an append query. Save it and run it.

Gavx · Feb 24, 2018

Thanks People,

Doc_Man, I should have mentioned that the intention is to render the amount (which comes as text) as numeric by deftly handling the plus sign.

Thanks Pat, you gave me some ideas. Unfortunately, linking won't work because the source file always changes.

The easiest solution is to after the import process, change the amount field properties from text to in my case currency. This deletes the plus sign. Not elegant but effective.

thanks again.

Pat Hartman · Feb 24, 2018

Unfortunately, linking won't work because the source file always changes.

I do this all the time. Just delete the existing link and link again. The key is to keep the "Access" table name the same so your queries and code don't break.

Gavx · Feb 24, 2018

Pat, I think your approach is the better way - I am going to start developing it.

thanks a lot

Gasman · Feb 24, 2018

I link to two Excel files and each week import the data from them. They are created from another system.
All I do is make sure they are the same names as when I first created the links.?

Gavx said:
Thanks People,

Doc_Man, I should have mentioned that the intention is to render the amount (which comes as text) as numeric by deftly handling the plus sign.

Thanks Pat, you gave me some ideas. Unfortunately, linking won't work because the source file always changes.

The easiest solution is to after the import process, change the amount field properties from text to in my case currency. This deletes the plus sign. Not elegant but effective.

thanks again.

Pat Hartman · Feb 24, 2018

When you use the TransferSpreadsheet method, you can specify the source file name And the Access table name.
If the file name changes and you need to have the user pick the file, here is code to bring up the file dialog box:

Code:

Public Function fChooseFile()
  
   ' Requires reference to Microsoft Office xx.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
 
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
             
      ' Set the title of the dialog box.
      .Title = "Please select one file"
 
      'starting location
      .InitialFileName = CurrentProject.path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Excel ", "*.XLSX"
''''      .Filters.Add "Access Databases", "*.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
        
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function

Mark_ · Feb 24, 2018

Gavx,

There may be reasons not to do so, but can you go into Excel and remove the offending "+"?

Removing leading positive symbol during text file import (1 Viewer)

Gavx

Registered User.

The_Doc_Man

Immoderate Moderator

Pat Hartman

Super Moderator

Gavx

Registered User.

Pat Hartman

Super Moderator

Gavx

Registered User.

Gasman

Enthusiastic Amateur

Pat Hartman

Super Moderator

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread