Home → Problems →

Problem with importing CSV

• Edit

Stalksy

03 Jan, 2012 08:53 PM

I have tried using the import CSV extension but I am not having any joy.

I tried using the text importer but that also didnt work.

I am using an iMac and Lion OS if that makes any difference.

Stalksy

1. 1 Posted by -Kevin N. on 03 Jan, 2012 09:04 PM

   

   Hi Stalsky,
   From what I understand, these importers do not work with Investment accounts.
   Having said that, can you attach a sample of a .CSV file that you are working with?
   Before attaching it, open it in a spreadsheet or simple word processor to make sure that it doesn't contain any sensitive data.
   -Kevin N.

   • Edit

2. 2 Posted by Stalksy on 04 Jan, 2012 03:11 AM

   

   Here is the file, I have created it in office 2011 Mac and saved in numerous file types.

   Thanks in advance

   • Workbook1.csv 431 Bytes
   • Edit

3. 3 Posted by Stalksy on 04 Jan, 2012 03:12 AM

   

   If anyone has a file that they have used and cleansed I would like to try coying and pasting into that file with my data.

   • Edit

4. 4 Posted by -Kevin N. on 04 Jan, 2012 01:45 PM

   

   Hi Stalksy,
   In your Workbook1.csv, I added YY to the Date format to DD/MM/YY.

   The amounts in the file appeared to be either charges or payments so I expressed them as negative numbers.

   The one problem that many users of the Text file iimporter run into is that there can only be one Amount column in the .csv file. If the file is created or downloaded with seperate Deposit & Credit columns, they must first be combined into one singular Amount column.

   Use the Text File Importer with the Workbook2.csv file that I've attached.
   Set the options as follows:
   First screen:
   Browse to the Workbook2.csv file.
   Set Encoding UTF-8
   Second screen:
   Account: user defined
   Fields Delimiter: [ , ]
   Date Format: DMY
   Decimal Point: .
   Third screen:
   Field 1: Date
   Field 2: Description
   Field 3: Amount

   Let me know how this works out for you. OK
   HTH Kevin N.

   • Workbook2.csv 500 Bytes
   • Edit

5. 5 Posted by Stalksy on 05 Jan, 2012 10:19 AM

   

   Hi Kevin,

   I imported your csv file which worked fine!

   I will export my data and copy and paste it into your file and let you know how it goes.

   Thanks.

   • Edit

6. 6 Posted by -Kevin N. on 05 Jan, 2012 02:09 PM

   

   Hi Stalksy,

   I'm glad to hear that it worked for you.

   I will export my data and copy and paste it into your file and let you know how it goes.

   I'm not sure it's going to be quite that easy or whether that will work for you. The Workbook2.csv file that I adapted for you is not a template file.

   You would need to manipulate your data so that the formatting matches the formatting in the Workbook2.csv file. For example, format the date to DD/MM/YY and express Debits as negative numbers.

   If this is a process that you are going to be repeating on an ongoing basis, then you should consider creating a spreadsheet template file. This would allow you the ease of just copying and pasting your data with the formatting being applied automatically.

   HTH -Kevin N.

   • Edit

7. 7 Posted by Akem F. on 09 Jan, 2012 08:24 AM

   

   Hi,

   I have a recurring problem when converting an excel spreadsheet to csv format. I have some columns of either 12- or 13-digit numbers. When I convert to csv and re-open the csv file, some particular numbers are always displayed in scientific notation.

   When I return to the excel document and open, the figures are not displayed in scientific notation.

   I have tried formatting the column width in the excel document and saving it. This works just fine in excel but when I save as a csv file and try to reopen it, it still opens with these particular cells displaying figures in the scientific notation.

   With the of presence these scientific notation, I am unable to carry out some analysis in the R-programming software.

   I will be grateful for any help and Thanks in advance.

   Akem

   • Edit

8. 8 Posted by -Kevin N. on 09 Jan, 2012 09:31 PM

   

   Hi Akem
   Try changing the formatting of all cells in Excel from 'General' to 'Number'.
   HTH -Kevin N.

   • Edit

9. 9 Posted by Akem F on 10 Jan, 2012 06:45 AM

   

   Hi Kevin,

   Thanks for the information. However, I have also tried formatting the cells in excel from "General to Number" but I still have the same problem. Any changes made in the excel file is saved. However, these changes are somehow not been identified by the csv format. So I keep having the same problem that particular cells continue to display figures in scientific notation even after changes have been made and saved.

   I will be grateful for further help and Thanks in advance.

   Akem

   • mfidata3.xls 73 KB
   • Edit

10. 10 Posted by -Kevin N. on 10 Jan, 2012 05:56 PM

    

    Hi Akem,
    I can not reproduce the problem that you are describing.
    I've attached a .csv file of your original mfidata3.xls file. It appears at first glance to be OK.
    If you continue to experience this issue, perhaps you would be best served by posting at the Microsoft Excel Community Forums.
    http://answers.microsoft.com/en-us/office/forum/excel?filter=answer...
    -Kevin N.

    • mfidata3.csv 41.8 KB
    • Edit

11. 11 Posted by Akem F. on 11 Jan, 2012 08:13 AM

    

    Hi Kevin,

    Thanks again for the help and the information. As you have suggested I will sent some posts to the Microsoft Excel Community Forums and hope to get some help from there.

    regards,
    Akem

    • Edit

12. System closed this discussion on 31 Mar, 2015 03:43 PM.