Программно импортировать таблицу Excel с указанием типов полей

@Kkarn · Регистрация: 02.12.2014

Author24 — интернет-сервис помощи студентам

Добрый день.
Допустим, есть таблица Excel, в которой несколько полей имеют довольно таки странные, заданные пользователем, форматы. Например, есть столбец "Размер", в котором хранятся сугубо ЧИСЛА, но с добавлением БУКВЫ, при этом буква добавляется через ФОРМАТ ЯЧЕЙКИ, т.е. в формате данной ячейки задано что-то вида 0"C", и если в ячейке хранится например число 12, то оно отображается как 12С. При этом в этом же столбце используются и куча других форматов типа 0"B", 0"D" и так далее. То есть в данном поле хранятся цифры, например: 12, 25, 14, но ОТОБРАЖАЮТСЯ они в виде 12С, 25D, 14C и так далее. Я без понятия, зачем оно так, но это - данность.

Так вот, существует задача "забрать" эти данные любым доступным способом в Access, чтобы эти буквы сохранились.

1. Делаю стандартно через Внешние данные, при этом хочу сделать присоединенную таблицу. При этом в мастере оно НЕ СПРАШИВАЕТ типы полей, делает вывод о содержимом по первым нескольким записям, и в итоге в присоединенной таблице в столбце Размер мы имеем числа: 12С, #Число!, 14C и т.д. При этом формат данного столбца Access устанавливает автоматически равным 0"C" (по первой ячейке), и соответственно все числа, которые не попадают в данный формат - вылетают как #Число! То есть такой вариант меня не устраивает.

2. Делаю через Внешние данные, но делаю импорт, а не присоединенную таблицу. В таком случае оно дает выставить для данного поля формат Текстовый - и все нужные буковки сохраняются. Ура-ура. НО

Возникает задача - как бы сделать такой вот импорт, с указанием типов полей, ПРОГРАММНО?..
DoCmd.TransferSpreadsheet - в нем я не нахожу, куда вписать, чтобы именно полю Размер задать текстовый формат...
Может, нужно воспользоваться чем-то другим?..
Заранее большое спасибо за подсказки)

mobile · 07.04.2015, 16:41

Kkarn, попробуйте запросом с IMEX=1. Этот параметр драйвера ISAM по идее должен выставлять в строки все. Пример ниже, путь и имена файла и листа, разумеется надо исправлять:

SQL

1	SELECT * FROM [Лист1$] IN 'c:\temp\frm.xls'[Excel 8.0; HDR=No; IMEX=1;]

@Kkarn · 07.04.2015, 16:43 **[ТС]**

mobile, То есть оно сделает текстовыми ВСЕ поля?.. Хм, тоже, конечно, не очень подходит, но гораздо лучше, чем терять буквы в нужном поле... Спасибо)
То есть метода VBA, аналогичного импорту файла штатными средствами (с указанием типов полей) - нет? Это жаль...

shanemac51 · 07.04.2015, 16:50

я тоже все импортирую в текстовом виде и уже в аксесс разбираюсь

@Kkarn · 07.04.2015, 16:56 **[ТС]**

shanemac51, В принципе, мне подходит и в текстовом виде. А как Вы импортируете - тоже запросами?..

Просто, допустим, у меня ситуация такая, что экселевских файлов может быть неопределенное количество, я думаю организовать что-то типа цикла по импорту их всех в базу во временные таблицы. И теперь вот думаю, как для этого бы приспособить данный запрос... Получается, мне нужно выполнить что-то вроде CurrentDB.Execute , построив запрос на создание таблицы на основе запроса, приведенного выше?

shanemac51 · 07.04.2015, 17:11

давненько правда не переводила ---забыла подробности

у меня было много ворд и ексель файлов в дикой набивке(хотя цехам были даны пустографки ---но почти все сдали существующие самобивки, не перебивали

время поджимало
поэтому сначала получила текстовик с добавлением имени файла и номера строки и кавычкой перед всеми полями
только потом импортировала

@Kkarn · 07.04.2015, 18:59 **[ТС]**

mobile, К сожалению, параметр IMEX=1 не сыграл. Построил запрос, он выполняется, но в результате в нужном поле выводятся сугубо цифры, без присоединенных в экселе форматом букв (((...

Добавлено через 8 минут
Самое во всем этом обидное, что если импортировать данные вручную, через Внешние данные, с помощью мастера, и указать для нужного поля формат Текстовый - все замечательно переносится... Но юзер же у меня не будет сидеть и импортировать вручную каждую таблицу в базу, суть вопроса как раз таки в том, чтобы максимально упростить обработку этих файлов... Эххх...

mobile · 07.04.2015, 19:23

Покажите здесь этот файл. Попробуем разобраться

@Kkarn · 07.04.2015, 19:43 **[ТС]**

Вот, я добавил во вложение файл прайса. В нем интересует например поле Размер. Хотя там и в Составе, кстати, то же самое творится с форматами...

@Kkarn · 07.04.2015, 19:56 **[ТС]**

У меня вообще такое параноидальное подозрение, что создатели данных прайсов специально ввели эти вот хитрые пользовательские форматы ячеек, чтобы их было затруднительно обрабатывать автоматически...

mobile · 07.04.2015, 20:16

Не знаю о чем Вы говорите, все переводится с буквами

SQL

1 2	SELECT * FROM [TDSheet$a2:i64000] IN 'C:\TEMP\Infinity Lingerie - BIG SIZE АПРЕЛЬ 2015.xls'[Excel 8.0; HDR=Yes;IMEX=1;]

@Kkarn · 07.04.2015, 20:36 **[ТС]**

Мистика... Ну, я мог бы выложить и свой скрин, без букв... Сейчас еще раз перепроверю...

Добавлено через 6 минут
Или я окончательно заработался, или... Вот жаль, что удалил я свой запрос уже, но я в нем видел своими глазами только цифры в обоих колонках, и в составе, и в размере... Кошмар)) Сейчас пытаюсь повторить - и ничего подобного, все время получается так, как нужно)))... Ей богу, это Ваше, mobile, благотворное влияние, не иначе)))) Еще раз спасибо огромное! Будем ковыряться дальше)

Добавлено через 7 минут
Не прошло и полгода, как говорится... Можно последний вопрос? поскольку он напрямую связан с данным прайсом, то задам его тут.
Мне необходимо сделать нечто вроде итогового запроса к данному прайсу, чтобы получалась группировка по наименованию товара, а в графу Размер собирались через запятую все значения размеров по данному наименованию... То есть чтобы получилось нечто вида:
Наименование 1 - размеры 12С,14А,18Е
Наименование 2 - размеры 1Е,12С
И так далее. Что-то я не соображу. Сюда же явно не подойдет итоговый запрос? в нем нет возможности собрать в одно поле значения нескольких через запятую же?.. А как тогда можно исхитриться? обработкой рекордсета в цикле?...

mobile · 07.04.2015, 21:05

Это достаточно обычный запрос на конкатенацию строк. Можно воспользоваться разными методами. Мне ближе метод, предложенный qwrqwr на другом форуме и модифицированный Minob'ом здесь.
Пишете запрос

SQL

SELECT Артикул, Наименование, 
       FieldToLine("ВашаТаблицаСПрайсами", "Размер", "Артикул", "Артикул='" & Артикул & "'") AS Размеры
FROM ВашаТаблицаСПрайсами
GROUP BY Артикул, Наименование

Нужно еще определить функцию, которую следует поместить в общий, стандартный модуль

Visual Basic

Public Function FieldToLine$(TableName$, FieldName$, FieldValGroup$)
Dim Adors As New ADODB.Recordset
Dim sq$
  sq = "SELECT " & FieldName & " FROM " & TableName & " WHERE " & FieldValGroup
  Adors.Open sq, CurrentProject.Connection, , , adCmdText
  If Not Adors.EOF Then FieldToLine = Adors.GetString(, , , ", ")
  Adors.Close
  If Len(FieldToLine) > 0 Then FieldToLine = Left$(FieldToLine, Len(FieldToLine) - 2)
End Function

Для отработки надо в референсах подсоединить библиотеку ADO - Microsoft ActiveX Data Objects 2.8 (или 2.6)

@Kkarn · 07.04.2015, 21:48 **[ТС]**

mobile, Ого-го) Это мощно... Чего только люди не придумают! Еще раз спасибо огромное. А я уже сделал из этого вопроса отдельную тему... Ну, почитаю еще и там для общего развития)

Добавлено через 12 минут
Попробовал. Сначала оно у меня ругалось на неверное число аргументов в функции. Когда убрал из вызова функции часть с "Артикул='" & Артикул & "'" - запрос сработал, но во всех строках выдало одну и ту же строку размеров, одинаковую для всех, и очень длинную. Наверное, я на сегодня уже просто заработался, пора спать, ничего не могу уловить...

Добавлено через 3 минуты
Ура-ура, я допер) в вашем примере нужно было убрать один "Артикул", оставить только "Артикул='" & Артикул & "'"

@Kkarn · 08.04.2015, 22:10 **[ТС]**

Уважаемый mobile, хочу еще раз поблагодарить за подсказку по поводу запроса в этой теме. А не могли бы Вы еще проконсультировать, как правильно вот этот наш запрос:

SQL

SELECT Артикул, Наименование, 
       FieldToLine("ВашаТаблицаСПрайсами", "Размер", "Артикул='" & Артикул & "'") AS Размеры
FROM ВашаТаблицаСПрайсами
GROUP BY Артикул, Наименование

прописать в коде VBA, поскольку я его буду делать через CurrentDB.Execute.

На моменте "Артикул='" & Артикул & "'" я, честно говоря, впал в небольшой ступор от количества кавычек и апострофов, которые, насколько я знаю, нужно удваивать, а так же и знака амперсанда (с которым я вообще не знаю, что делать, если честно...)...

В принципе, я раньше уже строил довольно обширные строки SQL для исполнения через CurrentDB.Execute, в том числе и встраивал в них имена переменных, но вот с этим запросом как-то туплю... (особенно с амперсандом... Его тоже удваивать надо?..)

mobile · 08.04.2015, 22:48

Сообщение от Kkarn

прописать в коде VBA, поскольку я его буду делать через CurrentDB.Execute.

Проще использовать сохраненный запрос:

Visual Basic

1	Currentdb.execute "СохраненныйЗапрос" 'если конечно запрос исполняемый, а не на выборку

@Kkarn · 08.04.2015, 23:15 **[ТС]**

mobile, К сожалению, сохраненный запрос не получится, т.к. я подставляю в строку SQL значения переменных из кода VBA...

Добавлено через 20 минут
Да, запрос исполняемый. У меня это запрос на добавление строк в другую таблицу. Но в нем вместо "Артикул" и т.д. я подставляю имена полей из переменных, потому и не могу использовать сохраненный запрос, а только такой, который строится "на лету"...

mobile · 08.04.2015, 23:23

Visual Basic

s="SELECT Артикул, Наименование, " _
& "       FieldToLine('ВашаТаблицаСПрайсами', 'Размер', 'Артикул='''' & Артикул & ''''') AS Размеры " _
& " FROM ВашаТаблицаСПрайсами " _
& " GROUP BY Артикул, Наименование"

@Kkarn · 09.04.2015, 17:00 **[ТС]**

mobile, Спасибо, попробую. Как все хитро с этими кавычками... Полночи они мне снились))

Добавлено через 6 часов 4 минуты
Черт, я совсем запутался... У меня все осложняется тем, что мне надо встроить в эту строку SQL имя переменной, в которой хранится имя поля в таблице (в котором хранятся данные об артикуле). Как так получилось:
Я подключаю таблицу экселя запросом, как обсуждалось ранее, при этом имена полей - НЕ из заголовков столбцов, поэтому они у нас вида "F2". То есть, например, в поле F2 таблицы у нас хранятся артикулы товара. Но, поскольку они НЕ ВСЕГДА хранятся именно в поле F2, то у нас есть что-то вроде настроечной формы, из которой мы в коде берем данные об имени поля артикула. То есть в коде это выглядит примерно так:

Visual Basic

1	Art = "F" & Me.[КолонкаАртикула] ' то есть мы в переменной Art получаем значение "F2"

И вот когда я пытаюсь в вышеизложенную строку SQL в коде запихнуть именно эту переменную Art, то в ходе моих манипуляций и игр с количеством кавычек и апострофов у меня получается либо вообще ошибка "Пропущен оператор", либо же в функцию FieldToLine в параметр FieldValGroup приходит строка

"F2=' & [F2] & '"

а нужна, к примеру, строка

"F2='123456'"

То есть в функцию передается строковое выражение с именем поля (взятое из переменной Art, а не само значение поля F2...

Добавлено через 2 часа 7 минут
И почему-то мне, чем больше я над этим думаю, тем больше кажется, что это нереально - встроить в эту строку имя переменной с именем поля так, чтобы оно передавало в вызываемую функцию именно ЗНАЧЕНИЕ из поля...

Добавлено через 13 минут
Мне тут подумалось, а что если изменить саму функцию?.. Так, чтобы передавать в нее не УСЛОВИЕ в виде выражения, а имя поля, из которого нужно брать значение для выражения, а само условие формировать уже в теле функции? Надо будет проверить.

mobile · 09.04.2015, 17:09

Если я правильно понимаю, то в исходном запросе должна быть не таблица, а сохраненный запрос, уже учитывающий все необходимые ограничения. И имя этого запроса передается в функцию. И вот в этом сохраненном запросе и надо задавать все требуемые условия:

SQL

1	FROM Сохраненный_Запрос_С_Условиями

И тогда не потребуются усилия, чтобы внедряться в функцию и что-то там менять. Сам исходный набор данных, построенный на сохраненном запросе, уже учтет все ограничения

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
		1
	Программно импортировать таблицу Excel с указанием типов полей 07.04.2015, 16:12. Показов 4685. Ответов 28 Метки нет (Все метки) Добрый день. Допустим, есть таблица Excel, в которой несколько полей имеют довольно таки странные, заданные пользователем, форматы. Например, есть столбец "Размер", в котором хранятся сугубо ЧИСЛА, но с добавлением БУКВЫ, при этом буква добавляется через ФОРМАТ ЯЧЕЙКИ, т.е. в формате данной ячейки задано что-то вида 0"C", и если в ячейке хранится например число 12, то оно отображается как 12С. При этом в этом же столбце используются и куча других форматов типа 0"B", 0"D" и так далее. То есть в данном поле хранятся цифры, например: 12, 25, 14, но ОТОБРАЖАЮТСЯ они в виде 12С, 25D, 14C и так далее. Я без понятия, зачем оно так, но это - данность. Так вот, существует задача "забрать" эти данные любым доступным способом в Access, чтобы эти буквы сохранились. 1. Делаю стандартно через Внешние данные, при этом хочу сделать присоединенную таблицу. При этом в мастере оно НЕ СПРАШИВАЕТ типы полей, делает вывод о содержимом по первым нескольким записям, и в итоге в присоединенной таблице в столбце Размер мы имеем числа: 12С, #Число!, 14C и т.д. При этом формат данного столбца Access устанавливает автоматически равным 0"C" (по первой ячейке), и соответственно все числа, которые не попадают в данный формат - вылетают как #Число! То есть такой вариант меня не устраивает. 2. Делаю через Внешние данные, но делаю импорт, а не присоединенную таблицу. В таком случае оно дает выставить для данного поля формат Текстовый - и все нужные буковки сохраняются. Ура-ура. НО Возникает задача - как бы сделать такой вот импорт, с указанием типов полей, ПРОГРАММНО?.. DoCmd.TransferSpreadsheet - в нем я не нахожу, куда вписать, чтобы именно полю Размер задать текстовый формат... Может, нужно воспользоваться чем-то другим?.. Заранее большое спасибо за подсказки) 0

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
	07.04.2015, 16:43 [ТС]	3
	mobile, То есть оно сделает текстовыми ВСЕ поля?.. Хм, тоже, конечно, не очень подходит, но гораздо лучше, чем терять буквы в нужном поле... Спасибо) То есть метода VBA, аналогичного импорту файла штатными средствами (с указанием типов полей) - нет? Это жаль... 0

shanemac51 Модератор 12057 / 4919 / 789 Регистрация: 07.08.2010 Сообщений: 14,396 Записей в блоге: 4
	07.04.2015, 16:50	4
	я тоже все импортирую в текстовом виде и уже в аксесс разбираюсь 0

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
	07.04.2015, 16:56 [ТС]	5
	shanemac51, В принципе, мне подходит и в текстовом виде. А как Вы импортируете - тоже запросами?.. Просто, допустим, у меня ситуация такая, что экселевских файлов может быть неопределенное количество, я думаю организовать что-то типа цикла по импорту их всех в базу во временные таблицы. И теперь вот думаю, как для этого бы приспособить данный запрос... Получается, мне нужно выполнить что-то вроде CurrentDB.Execute , построив запрос на создание таблицы на основе запроса, приведенного выше? 0

shanemac51 Модератор 12057 / 4919 / 789 Регистрация: 07.08.2010 Сообщений: 14,396 Записей в блоге: 4
	07.04.2015, 17:11	6
	давненько правда не переводила ---забыла подробности у меня было много ворд и ексель файлов в дикой набивке(хотя цехам были даны пустографки ---но почти все сдали существующие самобивки, не перебивали время поджимало поэтому сначала получила текстовик с добавлением имени файла и номера строки и кавычкой перед всеми полями только потом импортировала 0

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
	07.04.2015, 18:59 [ТС]	7
	mobile, К сожалению, параметр IMEX=1 не сыграл. Построил запрос, он выполняется, но в результате в нужном поле выводятся сугубо цифры, без присоединенных в экселе форматом букв (((... Добавлено через 8 минут Самое во всем этом обидное, что если импортировать данные вручную, через Внешние данные, с помощью мастера, и указать для нужного поля формат Текстовый - все замечательно переносится... Но юзер же у меня не будет сидеть и импортировать вручную каждую таблицу в базу, суть вопроса как раз таки в том, чтобы максимально упростить обработку этих файлов... Эххх... 0

mobile 26812 / 14491 / 3192 Регистрация: 28.04.2012 Сообщений: 15,782
	07.04.2015, 19:23	8
	Покажите здесь этот файл. Попробуем разобраться 1

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
	07.04.2015, 19:56 [ТС]	10
	У меня вообще такое параноидальное подозрение, что создатели данных прайсов специально ввели эти вот хитрые пользовательские форматы ячеек, чтобы их было затруднительно обрабатывать автоматически... 0

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
	07.04.2015, 20:36 [ТС]	12
	Мистика... Ну, я мог бы выложить и свой скрин, без букв... Сейчас еще раз перепроверю... Добавлено через 6 минут Или я окончательно заработался, или... Вот жаль, что удалил я свой запрос уже, но я в нем видел своими глазами только цифры в обоих колонках, и в составе, и в размере... Кошмар)) Сейчас пытаюсь повторить - и ничего подобного, все время получается так, как нужно)))... Ей богу, это Ваше, mobile, благотворное влияние, не иначе)))) Еще раз спасибо огромное! Будем ковыряться дальше) Добавлено через 7 минут Не прошло и полгода, как говорится... Можно последний вопрос? поскольку он напрямую связан с данным прайсом, то задам его тут. Мне необходимо сделать нечто вроде итогового запроса к данному прайсу, чтобы получалась группировка по наименованию товара, а в графу Размер собирались через запятую все значения размеров по данному наименованию... То есть чтобы получилось нечто вида: Наименование 1 - размеры 12С,14А,18Е Наименование 2 - размеры 1Е,12С И так далее. Что-то я не соображу. Сюда же явно не подойдет итоговый запрос? в нем нет возможности собрать в одно поле значения нескольких через запятую же?.. А как тогда можно исхитриться? обработкой рекордсета в цикле?... 0

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
	07.04.2015, 21:48 [ТС]	14
	mobile, Ого-го) Это мощно... Чего только люди не придумают! Еще раз спасибо огромное. А я уже сделал из этого вопроса отдельную тему... Ну, почитаю еще и там для общего развития) Добавлено через 12 минут Попробовал. Сначала оно у меня ругалось на неверное число аргументов в функции. Когда убрал из вызова функции часть с "Артикул='" & Артикул & "'" - запрос сработал, но во всех строках выдало одну и ту же строку размеров, одинаковую для всех, и очень длинную. Наверное, я на сегодня уже просто заработался, пора спать, ничего не могу уловить... Добавлено через 3 минуты Ура-ура, я допер) в вашем примере нужно было убрать один "Артикул", оставить только "Артикул='" & Артикул & "'" 0

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
	08.04.2015, 23:15 [ТС]	17
	mobile, К сожалению, сохраненный запрос не получится, т.к. я подставляю в строку SQL значения переменных из кода VBA... Добавлено через 20 минут Да, запрос исполняемый. У меня это запрос на добавление строк в другую таблицу. Но в нем вместо "Артикул" и т.д. я подставляю имена полей из переменных, потому и не могу использовать сохраненный запрос, а только такой, который строится "на лету"... 0

Программно импортировать таблицу Excel с указанием типов полей

Решение