Continuing with the New System Blues, I’ve been forced to start using Access 2010. A problem arose when I imported a .csv file. Access insisted on moving a column to where it shouldn’t be. The .csv file has dates in column J. Access has Field6, Field7, Field10, Field 8… I probably clicked on something I shouldn’t have, and now there’s no obvious way to make Access import a file in the proper layout. So can someone tell me the way to do it?
Also, I’d like to delete an imported file in the left pane. But right-clicking doesn’t have a Delete option and the Delete button (‘X’) on the tool bar is greyed out. How do I delete a file from Access 2010?
If your CSV file has a header row, then the import wizard can be told to use it for field names when importing the columns. If you have no header row, you’ll get automatically named fields (Field1, etc) - but you can easily rename them after importing by opening the table in design view.
One of the import stages allows you to select each column and tell Access what data type to use for it - if you don’t specify, it will guess - it’s pretty good at that, but it only looks at the top of the file and guesses from that - so if you have a column that contains numbers for the first hundred records, then has some rows where the same column contains text, Access might assign the column a numeric data type, and fail to properly import the text values.
I’ve never heard of it doing that spontaneously. If you’re in table view, you should be able to re-order the columns by clicking and dragging on their title bars (is it possible they got out of order by you accidentally doing this?)
I imported the file into Access, defining each field as Text.
I exported the file as .txt, defining the position and length of each field.
In Access 2003, this always worked. In Access 2010, for this file, the header record in the output file is wrong. The third field is shifted three bytes to the right. The Amount columns are defined as Text, and 12 bytes. Access 2010 writes the output file with the entire header cell value. For example, ‘PD 30 days dollars’ should output as ‘PD 30 days d’ because it’s defined as 12 bytes. (Headers don’t have to be ‘report quality’ because I’m reading the file with a program and writing output that does not have headers. Only I need to know what the columns are, and I do. So don’t worry about what the header says; just note that those fields should be 12 characters long and no more.) Of course having the header record positions shifted causes problems in the processing.
I have deleted the database, and have recreated it. When I look at the table in Access, it looks correct. When I look at the .txt file I exported, the first row is shifted. There is no reason Access 2010 shouldn’t do what I tell it to do! :mad:
I don’t have the same version of access to test this on (I am on 2016), but when I export a table as fixed-width text, I don’t see any option at all to create a header row - the ‘include field names on first row’ option is only offered for CSV (which by definition, has variable column widths)
I’m not ‘creating a header’. The header record is simply the first row in the .CSV file and Access should not treat it differently than any other record.
I suspect that Access 2010 doesn’t care what you define as a column width. Like most Microsoft products, it knows what you want to do better than you do and just does what it wants. My data in the data records fit within the allowed column widths before being imported into Access. Header records often do not; but that was OK in Access 2003 because Access 2003 accepted defined column widths when exporting to a text file. Access 2010 seems to ignore defined widths when a cell contains more characters than defined.
I’ve just confirmed that when a cell is longer than the field width I specify, Access 2010 ignores the specified width and just does what it wants. Bullshit! :mad:
Waitaminute… You’re saying that when you define or edit a field in Table Design as Short Text, and set the field size to (say) 10, you can subsequently enter more than 10 character in that field with no loss of data?
Or are you setting the ‘column width’ in Table View? That has no effect on the underlying table structure, only the way it looks in Table View. You have to open the table in Design view and set the field size.
Definitely. I import the .csv file into Access. For some things, I don’t care if the .csv field is longer than it should be because I know it will be truncated when I export to .txt. Until now. Let’s say I have a column called ‘Branch’. Since I wrote my programs back when the column was called ‘Div’ and the numbers in the column are 1-3 characters, I wrote the defined the field in my program as starting in position 6 for three bytes. Before Access 2010, I would import the file and the Access file (or ‘table’, I guess) would have ‘Branch’ in the first column. I export as text. ID is position 1 and width is 5. (It would be nice if I could export without having to have an ID, but anyway…) The ‘Branch’ field is defined as starting in position 6, and the width is defined as 3. Before Access 2010, the text file would have ‘Bra’ in position 6-8. Access 2010 ignores my definition of ‘6’ and ‘3’ and writes the whole ‘Branch’ in the text file, shifting the row to the right. So it’s ignoring what I tell it.
MS Access is paying attention to what you DO, not what you SAY (when they conflict).
You have said that this field should be 3 characters wide, but you have actual data in it that is 6 characters wide. So it goes by the actual size of the data, overriding your (incorrect) defining of the field. That is the safest option, to prevent truncating & losing data. Humans frequently make errors in defining data; this default action saves the data in such cases.
For good system design, you should not depend on undocumented default actions when there is a data conflict, but specifically code the actions you want to happen. If the field may contain longer data items, but you only want the first 3 characters, you should specifically truncate the data on the right to 3 characters.
2003 allowed me to truncate the data on export. If 2010 can’t do that, then it’s badly programmed.
I’ve been talking about the header record, but it’s more involved than that. One of the things I do is normalise the formats and syntax of business names and addresses. Before I started writing these programs, people would have to edit the files manually in Excel. My programs clean up the old records so that I only have to clean up the new ones. It’s to the point where I might have a 50,000 or 100,000 record file, and only a handful of records need to be cleaned up after my cleanup program. Here’s the thing: One of the output formats has a limit of 40 characters for the Name fields and 30 characters for the Address fields. For a long time I made sure the input data would fit. Nowadays, with the number of new records being small after the cleanup program, and given that most data fits within the allotted space, I don’t always ensure the data fits. It’s quicker and easier to just fix the occasional truncated data. If Access 2010 cannot obey instructions, then I’ll have to take the time to look out for truncations in the input file. I don’t want to do that because, as I said, it’s easier to check/fix on the back end.
I could expand the fields for the cleanup, making sure they will fit in the older output format, and I have done on more recent programs. But if I have to do that for every account, then I’ll be rewriting hundreds of programs.
So is there a way to make Access 2010 do what I tell it to do, and truncate fields on export to text like Access 2003 did? Or am I going to have to beg our IT supplier to load 2003 on my computer, which they said they ‘can’t’ do?
I think maybe the best workable solution may be to create a template table with the exact field properties and sizes (in Table Design view, by setting field size), then import your data into this table (that is, import to existing table, rather than importing and creating a table on the fly) - that will absolutely impose field sizes on your data (you’ll likely experience some truncation of data on import as a result)
Alternatively, you could create a query that uses the Left() function to present a properly-trimmed view of your original imported data, then use that query for your fixed width export.
I’m not really sure what to suggest beyond that. I’ve worked a lot with Access, and the workflow you are using is not something I’m familiar with - I’m not going to say you’re doing it wrong, because I can’t tell - but you are doing things differently from anything I’m familiar with.
I have no idea how to do that. Also, I import/export data into its own database so I’d have to do this a hundred times. I don’t care about truncation – in fact, it appears that I depend on it – because I know what I’m doing with the data.
The only reason I use Access at all is because Easytrieve requires data to be in fixed-position text files and we generally get data in Excel spreadsheets. Since Excel can’t write fixed position text files because of the length of the records (it either truncates the records, or else puts the long ends at the bottom of the file, depending on the option), Access is the only way I can make the fixed-position text files. Most people use Access for – well, whatever Access was made for. But for me, it’s just so that I can make files my programs can use.
It sounds like my options are to plead my case for Access 2003 and hope IT installs it for me, rewrite all of my programs to read larger fields to account for a program that thinks it’s smarter than I am*, or else go back to the old practice of truncating the data before import/export.
*The program thinking it know better than I do is a PITA in Excel too.
My Access 2003 databases were .mdb format. The Access 2010 databases are .accdb format. It looks like Access 2010 will not allow me to delete tables from .mdb format, so I’m creating new databases for each account.