Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    lcummings is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    8

    Type Conversion

    I'm trying to import a CSV into Access as a Text File. However, the data set imports with errors, where all Fields that are Numeric Values/Currency Values populate an error of Type Conversion Failure.



    My data set has 4 columns that have numeric values for revenue and impression numbers. I assume this is error is being generated because the wrong data type is being selected in the upload process. What data set type should numbers below have? I'm trying to upload them all as long integers, but it's not working.

    5,666,445
    $55,666,667.08

    Can you please help? Thanks.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Integers are whole numbers. They do not allow for decimals.

    Is that what your data really looks like in the CSV file? If so, are those values enclosed in a text-qualifier like double-quotes?
    If not, how is Access supposed to differenitate the commas in your numbers from the commas that are delimiters?

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    lcummings is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    8
    1 Power BI- Forum Upload.zip

    Sure, I've attached an example data set that mimics my upload and included a screenshot below in case the zip file doesn't open. Thanks.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	60.5 KB 
ID:	32448

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Unfortunately, I cannot download files off of the internet from my current location.
    You said it is a CSV file, right? But you posted an Excel screen shot. That does not give us a true indication of what the data looks like, as Excel does its own automatic conversions to CSV files.
    Can you open the CSV file in a Text Editor, like NotePad or WordPad, and post the first few lines of the data here?

  6. #6
    lcummings is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    8
    That screenshot was a CSV, I had just expanded the columns to be read-able. I've attached it a notepad with the csv data. Thanks.

    1 Power BI- Forum Upload Text.zip

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    That screenshot was a CSV, I had just expanded the columns to be read-able.
    That is viewing it in Excel. If you really want to see what the unaltered data in a CSV looks like, do NOT use Excel to view. Excel actually does its own automatic conversions upon opening it. So it does not give you a true picture of how the data is actually structured in the CSV. (I don't know why Microsoft decided that Excel should be the default program to view CSV files instead of NotePad. IMO, that is a very poor decision, and one of the first things I do with a new computer is change the default programs so that the default program to open a CSV is something other than Excel).

    Like I mentioned before, I cannot download internet files from my current location (my workplace security prohibits it), so I won't be able to download the file until I get home.
    I was hoping that you could just open the CSV in NotePad, and copy and paste the first few rows here so we can see what it looks like without having to download anything.

  8. #8
    lcummings is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    8
    Thanks, please see below for the first few lines of data.

    Date Country Ad unit Line item type Advertiser Creative size Key-values Ad unit code Total code served count Total impressions Total clicks Total CPM and CPC revenue ($)
    1/25/2018 United States Test Test Test 320 x 50 test test 0 "875,566" 423 "14,665.73"
    1/25/2018 United States Test Test Test 321 x 50 test test 0 "479,616" 228 "8,033.57"
    1/25/2018 United States Test Test Test 322 x 50 test test "922,585" "6,272,342" "16,576" "6,363.13"
    1/25/2018 United States Test Test Test 323 x 50 test test "903,268" "2,784,660" "10,674" "5,696.09"
    1/25/2018 United States Test Test Test 324 x 50 test test 0 "423,526" "3,099" "5,169.99"
    1/25/2018 United States Test Test Test 325 x 50 test test "2,515,890" "1,841,970" "12,838" "4,912.15"
    1/25/2018 United States Test Test Test 326 x 50 test test 0 "1,116,535" 0 "4,289.37"
    1/25/2018 United States Test Test Test 327 x 50 test test 0 "842,577" 0 "3,992.08"
    1/25/2018 United States Test Test Test 328 x 50 test test 0 "317,446" "2,318" "3,906.30"
    1/25/2018 United States Test Test Test 329 x 50 test test "1,945,182" "1,651,213" "7,536" "3,859.19"
    1/25/2018 United States Test Test Test 330 x 50 test test "471,120" "415,413" "9,101" "3,614.57"
    1/25/2018 United States Test Test Test 331 x 50 test test "441,765" "3,082,670" "5,562" "3,224.23"
    1/25/2018 United States Test Test Test 332 x 50 test test "421,480" "411,093" "4,330" "2,977.42"
    1/25/2018 United States Test Test Test 333 x 50 test test "1,265,309" "1,452,807" "4,668" "2,947.79"
    1/25/2018 United States Test Test Test 334 x 50 test test "1,213,118" "255,531" "2,399" "2,737.03"
    1/25/2018 United States Test Test Test 335 x 50 test test 0 "169,619" "1,228" "2,722.38"
    1/25/2018 United States Test Test Test 336 x 50 test test "1,021,788" "2,391,432" "9,032" "2,659.62"
    1/25/2018 United States Test Test Test 337 x 50 test test "1,252,844" "1,151,361" "3,375" "2,580.21"

  9. #9
    lcummings is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    8
    I realized that the person who created this report generated it as Excel CSV, which is throwing off all the formatting. If this report is edited to export as CSV, this should solve the import errors, correct?

  10. #10
    lcummings is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    8
    This is how it's coming out as CSV in a text pad. Hopefully you can help with these lines below.


    Date Ad unit Line item type Ad unit code Total code served count Total impressions Total clicks Total CPM and CPC revenue ($)
    1/30/2018 web_weather_ar_bh » local_forecasts » 10_day Price priority 10_day 11 10 0 0
    1/30/2018 web_weather_ar_bh » local_forecasts » 10_day House 10_day 11 11 0 0
    1/30/2018 web_weather_ar_bh » local_forecasts » 10_day Ad Exchange 10_day 42 39 0 0.01
    1/30/2018 web_weather_ar_bh » local_forecasts » today Price priority today 1 1 0 0
    1/30/2018 web_weather_ar_bh » local_forecasts » today House today 1 1 0 0
    1/30/2018 web_weather_ar_bh » local_forecasts » today Ad Exchange today 8 8 0 0.01
    1/30/2018 web_weather_ar_bh » local_forecasts » today - today 2 2 0 0
    1/30/2018 web_weather_ar_dj » local_forecasts » 10_day House 10_day 21 21 0 0
    1/30/2018 web_weather_ar_dj » local_forecasts » 10_day Ad Exchange 10_day 2 2 0 0
    1/30/2018 web_weather_ar_dj » local_forecasts » 10_day - 10_day 1 1 0 0
    1/30/2018 web_weather_ar_dj » local_forecasts » today House today 3 0 0 0
    1/30/2018 web_weather_ar_dj » local_forecasts » today Ad Exchange today 3 3 0 0
    1/30/2018 web_weather_ar_dj » local_forecasts » weekend House weekend 3 3 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts Price priority local_forecasts 1 1 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts House local_forecasts 14 14 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts Ad Exchange local_forecasts 6 6 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » 5_day Price priority 5_day 2 2 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » 5_day House 5_day 12 12 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » 5_day Ad Exchange 5_day 4 4 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » 10_day House 10_day 92 92 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » 10_day Ad Exchange 10_day 10 10 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » today Price priority today 15 10 0 0.02
    1/30/2018 web_weather_ar_dz » local_forecasts » today House today 112 93 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » today Ad Exchange today 67 50 0 0.02
    1/30/2018 web_weather_ar_dz » local_forecasts » today - today 13 10 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » hourly Price priority hourly 2 2 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » hourly House hourly 18 18 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » hourly Ad Exchange hourly 2 2 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » hourly - hourly 1 1 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » maps Price priority maps 8 8 0 0.01
    1/30/2018 web_weather_ar_dz » local_forecasts » maps House maps 77 77 0 0
    1/30/2018 web_weather_ar_dz » local_forecasts » maps Ad Exchange maps 43 43 0 0.02

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    That does not look like a CSV at all. CSV stands for "Comma Separated Value", meaning each field is separated (delimited) by a comma.
    But that file appears to be space-delimited, not comma-delimited.

    The only commas in the files are in the numbers (which are surrounded by text qualifiers of double-quotes, meaning all commas contained between them are treated as literal data and not delimiters).
    So, it looks like they are at least handling the numbers all right, but the delimiters are an issue.

    I realized that the person who created this report generated it as Excel CSV, which is throwing off all the formatting. If this report is edited to export as CSV, this should solve the import errors, correct?
    What program are they exporting from? I never heard of an "Excel CSV". Usually, it is one or the other (Excel or CSV), but not both.
    I would try having them export as a CSV and see if that works an better (they may want to check to see if they can choose the delimiter - some programs let you use something other than a comma for a delimiter).

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    This is how it's coming out as CSV in a text pad. Hopefully you can help with these lines below.
    Once again, does not look like a typical CSV (do you see any commas?).

  13. #13
    lcummings is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    8
    The advertising solution we're using downloads CSV as Excels rather than texts but I was able to download it as Text. Please see below with commas


    Date,Ad unit,Line item type,Ad unit code,Total code served count,Total impressions,Total clicks,Total CPM and CPC revenue ($)
    1/30/18,web_weather_ar_bh » local_forecasts » 10_day,Price priority,10_day,11,10,0,0.00
    1/30/18,web_weather_ar_bh » local_forecasts » 10_day,House,10_day,11,11,0,0.00
    1/30/18,web_weather_ar_bh » local_forecasts » 10_day,Ad Exchange,10_day,42,39,0,0.01
    1/30/18,web_weather_ar_bh » local_forecasts » today,Price priority,today,1,1,0,0.00
    1/30/18,web_weather_ar_bh » local_forecasts » today,House,today,1,1,0,0.00
    1/30/18,web_weather_ar_bh » local_forecasts » today,Ad Exchange,today,8,8,0,0.01
    1/30/18,web_weather_ar_bh » local_forecasts » today,-,today,2,2,0,0.00
    1/30/18,web_weather_ar_dj » local_forecasts » 10_day,House,10_day,21,21,0,0.00
    1/30/18,web_weather_ar_dj » local_forecasts » 10_day,Ad Exchange,10_day,2,2,0,0.00
    1/30/18,web_weather_ar_dj » local_forecasts » 10_day,-,10_day,1,1,0,0.00
    1/30/18,web_weather_ar_dj » local_forecasts » today,House,today,3,0,0,0.00
    1/30/18,web_weather_ar_dj » local_forecasts » today,Ad Exchange,today,3,3,0,0.00
    1/30/18,web_weather_ar_dj » local_forecasts » weekend,House,weekend,3,3,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts,Price priority,local_forecasts,1,1,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts,House,local_forecasts,14,14,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts,Ad Exchange,local_forecasts,6,6,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » 5_day,Price priority,5_day,2,2,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » 5_day,House,5_day,12,12,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » 5_day,Ad Exchange,5_day,4,4,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » 10_day,House,10_day,92,92,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » 10_day,Ad Exchange,10_day,10,10,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » today,Price priority,today,15,10,0,0.02
    1/30/18,web_weather_ar_dz » local_forecasts » today,House,today,112,93,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » today,Ad Exchange,today,67,50,0,0.02
    1/30/18,web_weather_ar_dz » local_forecasts » today,-,today,13,10,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » hourly,Price priority,hourly,2,2,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » hourly,House,hourly,18,18,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » hourly,Ad Exchange,hourly,2,2,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » hourly,-,hourly,1,1,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » maps,Price priority,maps,8,8,0,0.01
    1/30/18,web_weather_ar_dz » local_forecasts » maps,House,maps,77,77,0,0.00
    1/30/18,web_weather_ar_dz » local_forecasts » maps,Ad Exchange,maps,43,43,0,0.02
    1/30/18,web_weather_ar_eg,Ad Exchange,web_weather_ar_eg,4,4,0,0.00
    1/30/18,web_weather_ar_eg » local_forecasts,Price priority,local_forecasts,2,2,0,0.01
    1/30/18,web_weather_ar_eg » local_forecasts,House,local_forecasts,3,1,0,0.0

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I am not sure what the " » " in your data are, if those are tabs or soft carriage returns, but those could present problems for importing data into Access.
    Try taking just the first few rows of your data in a new text file, manually delete those characters, and try importing that into Access and see if you have any issues.

  15. #15
    lcummings is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    8
    I was able to import my data but the only errors I'm getting are where there's no value inputted for a specific metric so it's null. How do I allow access to register this null value as fine and not an error?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Type Conversion Failure
    By jessgold in forum Access
    Replies: 5
    Last Post: 11-12-2012, 10:39 PM
  2. Type conversion error
    By corymw in forum Access
    Replies: 1
    Last Post: 07-25-2012, 11:55 AM
  3. Help need: Type conversion failure
    By tami in forum Access
    Replies: 1
    Last Post: 01-18-2012, 07:18 AM
  4. Type Conversion Failure
    By fpmsi in forum Access
    Replies: 7
    Last Post: 09-22-2011, 11:25 AM
  5. Data Type Conversion in Query
    By EHittner in forum Queries
    Replies: 3
    Last Post: 04-14-2010, 02:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums