Removing leading positive symbol during text file import

[Gavx]

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]

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.

 

[Gavx]

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.

 

[Gavx]

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

thanks a lot

 

[Gasman]

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.?

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.

 

[Mark_]

Gavx,

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