Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    In Post #11, I attached the imported Excel to the Access table as a zip file. But when I imported the modified Excel file to MS Access all kinds of problems occurred. That was the purpose of the post.

    I thought in that system, I converted all numbers to text and then I imported the all text Excel file to MS Access. The result is attached to Post # 11. It clearly was not the same, the Access table looked very different than the Excel worksheet.

    You say it worked. How did you do it? The apostrophe did not make it over. That is alright. It would have been taken out anyway.

    But the rest.

    Any help appreciated. Thanks in advance.



    Respectfully,

    Lou Reed

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Just used the Import wizard.

    As I said, all data came over but SiteCount 1 through 49 ended up at the end of the imported records. Which of itself is bizarre.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    You used the import Wizard in MS Access. I guess that would work.I trued the ExcelImport.ImportExcelSpreadsheet command. It worked on smaller projects, on this Production Tracker spreadsheet not so much.

    What could be the difference. The apostrophe did not move over, but the rest did.

    Respectfully,

    Lou Reed

  4. #19
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The apostrophe did not move over, but the rest did.
    That is because in Excel, the apostrophe is not really part of the data, even though it look like it is - in the bar at the top of the spreadsheet. Notice you don't see the apostrophe in the cell itself. The apostrophe acts as a "flag", telling Excel to treat the cell data as text, no matter what is entered. Since it is not really a part of the data, it doesn't get imported to Access.

    If you really DO want an apostrophe to be imported to Access, type it twice in the Excel value.

  5. #20
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    June7 -

    As I said, all data came over but SiteCount 1 through 49 ended up at the end of the imported records. Which of itself is bizarre.
    Strange, yes, but normal. I have seen it in other applications I have used. Remember that when Access adds records to a table, it puts them more or less wherever it feels like putting them. There is no inherent order, i.e. 'record number', so when the table is opened, the data can appear to be scrambled. In order to guarantee that the records can be viewed in the "same order" as the spreadsheet, there has to be data in the spreadsheet that allows them to be ordered correctly in Access.

    I found it bizarre too, until I solved the "problem"!

    Cheers!

    John

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That does make sense. I've just never noticed an Excel worksheet to import like but then I don't do it routinely and not a sheet with such a mish-mash of data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #22
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you can see the same effect when copying a large number of files from one directory to another. If you watch how they are copied, it seems to do a few files from the top, then some from the bottom, then back to the top again or perhaps the middle. I think it is to do with memory/disk efficiency - effectively block reading/writing. With disk it is easier to imagine - files get fragmented. I don't know, but suspect that excel stores data randomly in memory - after all users constantly move data around the worksheet/book. No reason to believe Excel moves it around in memory as well, but just updates the pointers for each bit of data. I wrote a very basic spreadsheet program many years ago and long before Excel (it was 64 columns by 64 rows!) and that was how I did it.
    Last edited by CJ_London; 12-13-2017 at 06:42 AM.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-10-2015, 11:53 AM
  2. Replies: 3
    Last Post: 05-09-2013, 09:17 AM
  3. Compile Error: Can't find out what's wrong with the code.
    By vickan240sx in forum Programming
    Replies: 4
    Last Post: 05-24-2012, 06:25 PM
  4. Compile error: code or function not defined
    By GeorgeBrown in forum Access
    Replies: 1
    Last Post: 09-19-2011, 10:25 AM
  5. Replies: 23
    Last Post: 03-26-2009, 06:50 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