The advanced button only gives two columns, field name and skip. If I choose to import to a new table then the advanced button will show other options such as field type. Am I missing something? Is there a way to have the field type appear when importing to an existing table? I can then save my options as a specification for future uploads.
I did change the load file from .csv to .txt and amazingly it worked! I am very grateful...
jenniferhelen
Last Activity: Jan 30 '11, 06:18 PM
Joined: Apr 5 '09
Location:
-
Hello MMcCarthy,
It is definately a text field format (see attached file, field Loc) that is why I find this so frustrating. I have read on a few other posts that Access looks at the first few records in a load to determine the field type and this seems to be exactly what Access is doing here. The solution written on the other post was to predefine a table with the formats I desire. I have the predefined table and still it is not working....Leave a comment:
-
jenniferhelen started a topic Why are leading zeros dropped on a import to Access 2007 from a .csv file?in AccessWhy are leading zeros dropped on a import to Access 2007 from a .csv file?
I am working in Access 2007 and trying to import a .csv file. One of the fields in the .csv file, called Locations, contains numbers with leading zeros and also the last record contains letters. I am importing into a predefined table and the field Locations is set up with Text formatting. When I import my file, the leading zeros are lost and the last record that contains letters is not loaded. The error I receive is “Type Conversion”. ... -
ADezii,
Thanks again for the code and example database. It works perfectly and beautifully in the example. I am in the process of implementing it in my database. I really appreciate all your help.
Take care,
JenniferLeave a comment:
-
Thanks everyone for your help.
JenniferLeave a comment:
-
Thanks NeoPa for your reply again.
I don't have any fields selected when I paste and I found the transpose function to work successfully when I reduce the amount of data to 100 columns. I wonder if I am running into a column limitation in Excel? If the transpose works successfully I should have 317 columns. I am working with Excel 2003. Are you aware of any limitation here?
Thanks again
JenniferLeave a comment:
-
ADezii,
I am extremely grateful for all the time you spent on my problem. I have spent much time trying to use it but I am very inexperienced in this area. Whenever I try to run the code, I am prompted with a macro box. I do not have any macros in this database so I am not sure what is expected at this point. I entered an "a" at this prompt and then selected the Create button (the only other option was the Cancel button). The...Leave a comment:
-
How can I transpose a table/query in Access?
I am working with a query that has 6 columns and 101 rows; I would like to transpose the rows and columns. I have tried using a crosstab query but Access limits the row "fields" to 3 and this was not producing the desired output. I saw a comment in a thread about using a pseudo row field as a work around but I do not understand how this works.
Another option I tried is from the following link and attempting method 2: http://su... -
How do you successfully transpose rows into columns in Excel?
I have been searching threads for a while and found the instructions listed below many times, however when I get to step 6 and select to save, I always receive the following error, "The information cannot be pasted because the copy area and the paste area are not the same size and shape". Does this function only work if the number or rows and number of columns are the same number? Does anyone have any suggestions for me? I greatly appreaciate... -
NeoPa and OldBirdman,
Thanks so much for your help. The "OR" in place of "And" did the trick.
Thanks again for your exertise.
JenniferLeave a comment:
-
Thank you for your suggestions. I have them half working. When I set the criteria in the five columns to = 0, the result is 224 records. So now I know I want to surpress 224 records out of 569 total records. However when I change the =0 to <>0, I do not get any results. My where statement is below.
The data in these columns may offset each other so I am unable to use ADezil's suggestion. I appreciate very much your comments....Leave a comment:
-
Suppress records in Access
I would like to suppress a row of data if each of my 5 currency columns contains a zero. An example of 1 record is below:
So if columns 4 through 8 all contain zeros then I don’t want this record to appear in my report. I am not sure if this is even possible as I have been unable to find any documentation...Code:Col 1, col 2, col 3, col 4, col 5, col 6, col7, col 8 Smith, 2008, qtr4, 0, 0, 0, 0, 0
-
Yes that makes sense. I have made those changes. Thanks again.Leave a comment:
-
Thank you, thank you, thank you! I don't even want to tell you how much time I spent on that!
Just out of curiosity, the fields are listed as "numbers" in the original table. However I have them in initial query before appearing in the final query where I am performing the calculations. Does the format not carry over from the table? Also, in the query I tried changing the format to "general number" or "standard...Leave a comment:
-
query calculation problem
I am having a problem with a calculation in Microsoft Access. I am trying to add two numeric columns, however the result I am receiving is the data is being concatenated. I am using the following formula: newField:([field1]+[field2]). If field1 contains 567 and field2 contains -98, my result is 567-98 instead of 469. I have performed simular calcualtions many times and I am going nuts trying to figure out why this won't work. I appreciate...
No activity results to display
Leave a comment: