Importing Excel data to Access

[kinda]

Hi All,
I have an Excel spreadsheet that I need to import to Access. I used the DoCmd.TransferSpreadSheet command but the problem I am facing is that all the field names are in Colums A1 to A15 and the data is in Columns B1 to B15. This is unlike the regular way of storing data where the fieldnames are across from A1 to P1 and the data is at the row level. Can anybody help me out?

Thanks.

Kinda.

[PEB]

You can do:
1. A VB script on Excel to convert your data as usual
2. Import data as it is and then Convert it under Access, but your data would'nt exceed 255 columns, pardon rows ;)

And so how to convert your data without using VB script under Access?

I'll answer you tomorrow if you don't have any idea! You have to use the Switch function :) and an empty table with the number of records that you have in your Excel and first column with the numbers from 1 to your number of records!

Each record in your Excel database have to have a number from 1 to your number of records!

Do you have an idea how to continue?

When you have done those things on Access can you tell me your 2 tables with fields to write you the SQL that will transform your table in real table :)

[Jliu6]

This way may be easier:
- in Excel, select both columns, copy & paste special (transpose), so the first row becomes column header and the 2nd row is data

- import to your Access db

[kinda]

Thanks for the info but the problem is that I have over 2500 Excel files I need to import into Access and I want to do that programmatically. If I can find a way to transpose the first column into the first row and the second column into the second row I will be very happy.

Thanks again for the quick response..

[PEB]

So it's a bit hard! And if you have more than 255 columns, it will be impossible with Access!

It's very important to know if the number of columns depass 255 or not!

IF the columns are less than 255 so I'll pass you a function in Access and SQLs that will help you!

[PEB]

So it's so heavy your task!

But I think you can do something like this as algorithme:

1. Import your 1 table from Excel to Access with transfer spreadsheet as You have done it

2. Use SQL to treat this table and to convert it as you want

3. Delete the results from your temporary tables in Access

4. Import the next one table from Excel

About step 1, 3 and 4 I obviously see that you can do them!

About step 2 yesterday I began to give you some ideas!

For step 2 you need:

- 1 table where to store your data from Excel with near 255 fields inside
- the enumeration that I suggest you yesterday isn't usefull way!
- You need a function that returns the number of the respective row
- You need a function that returns the information of a column N
- 1 table to store the numbers from 1 to 255
- 1 table to store the ready results!

- SQL statement that will transform the table and append the new table in the table with results!

So please create the 3 tables, send me information of them and I'll send you the information about the 2 functions and the SQLs!

If have any questions about the others steps please tell!

[PEB]

Here the first function:
Function show_field_inormation(SQLS, Field_N, Row_N)
On Error Resume Next
Dim mydb As Database
Dim myr As Recordset
Dim result
'Stop
Set mydb = CurrentDb()
'Debug.Print "show_f_cln_start" + Str(Now)
Debug.Print SQLS
Set myr = mydb.OpenRecordset(SQLS)

If (myr.RecordCount = 0) Or (myr.RecordCount < Row_N) Then
show_field_inormation = ""
Exit Function
End If

myr.MoveFirst
myr.Move Row_N
result = myr(Field_N)

myr.Close
'Debug.Print "show_f_cln_end" + Str(Now)

mydb.Close
show_field_inormation = result

End Function

And the second:

Function show_row_n(SQLS, Field_N, Searched)
On Error Resume Next
Dim mydb As Database
Dim myr As Recordset
Dim myupd_current
Dim myupd_last
Dim result
Set mydb = CurrentDb()
Set myr = mydb.OpenRecordset(SQLS)

If myr.RecordCount = 0 Then
show_row_n = 0
Exit Function
End If

myr.MoveLast
myupd_last = myr.RecordCount
myr.MoveFirst
For myupd_current = 1 To myupd_last
If myr(Field_N) = Searched Then
show_row_n = myupd_current
myr.Close
mydb.Close
Exit Function
End If
myr.MoveNext
Next myupd_current
show_row_n = 0
myr.Close

mydb.Close

End Function


For the SQL I have to have more information about your 3 tables!

[PEB]

Imagine that in Table1 you have been transfered your Excel file. The columns of this table are f1, f2, f3, f4, f5 and so till f250

Imagine Table2 is a table with only one field field1. In Table2 you have to insert 250 rows! With the numbers from 1 to 250! This field have to be numeric! To generate those numbers you can use Excel and copy the values from Excel!

Table3 will be your table with the ready results! Here you will have a field: Table_index to save from which excel file comes the information and columns till the number of columns in your tables! For the exemple 6
So Table3 will have Table_index, f1,f2,f3,f4,f5,f6


So imagine that table1 contains the follow:

f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
Mycolumn1 1 2 3 4 5 6 7 8 9
Mycolumn2 Iop Ioq Ios Iod Iof Iog Ioh Ioj Iok

Table2

Field1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252

Table3
Table_index Field1 Field2 Field3 Field4


So we can proceed!

SELECT Table1.f1, show_field_inormation("Table1",[Field1],0) AS Mycolumn1, show_field_inormation("Table1",[Field1],1) AS Mycolumn2
FROM Table1, Table2
WHERE (((Table1.f1)="Mycolumn1") AND (Not (show_field_inormation("Table1",[Field1],0)) Is Null) AND (Not (show_field_inormation("Table1",[Field1],1)) Is Null));

If you try this query, the result be the next:

f1 Mycolumn1 Mycolumn2
Mycolumn1 1 Iop
Mycolumn1 2 Ioq
Mycolumn1 3 Ios
Mycolumn1 4 Iod
Mycolumn1 5 Iof
Mycolumn1 6 Iog
Mycolumn1 7 Ioh
Mycolumn1 8 Ioj
Mycolumn1 9 Iok

[PEB]

In this case you have to know the number of all columns /rows in Excel/ to mention them in the query

Your problem was pasionate! IF you have other questions, please don't wait!