При импорте в Access из Excel Ошибка типа данных (#Число!)

@Badya1991 · Регистрация: 19.02.2016

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

Добрый день, есть запрос на добавление данных из файла excel, выгруженного из 1С, в базу данных MS Access, по столбцам в существующую таблицу:

SQL

INSERT INTO БДР ( Организация,
 Подразделение,
 Субконто2,
 Контрагент,
 СчетДт, СчетКт )
SELECT [TDSheet$].Организация,
 [TDSheet$].Подразделение,
 [TDSheet$].Субконто2,
 [TDSheet$].Контрагент,
 [TDSheet$].СчетДт,
 [TDSheet$].СчетКт
FROM [Excel 12.0 XML;HDR=YES;IMEX=1;DATABASE=C:\Users\...\БДР.xlsx].[TDSheet$];

Проблема: MS Access при загрузке данных путем этого запроса определяет тип данных по первым 8 строкам, в столбце СчетДт первые 8 строк содержат число 20 (20 счет), далее есть счет 90.01.1 и тд, эти данные он не понимает и выдает ошибку #Число!, как можно решить эту проблему и загрузить это поле сразу текстовым, то есть по умолчанию задать тип данных текст?

Добавлено через 1 час 0 минут
Я искал уже но не нашел, вот типа в этой теме примерно такая же ошибка, Как изменить тип данных поля при импорте, но суть в том что ошибка возникает на этапе выборке то есть в запросе, то есть таблица уже обновляется данными с ошибкой, получается импортировать если создается новая таблица через мастер импорта и экспорта но я там не знаю как преобразуются данные на этапе запроса.

@snipe · 20.01.2017, 04:12

надо в excel этот столбец сделать текстовым до загрузки

Добавлено через 59 секунд
или же в запросе воткнуть по этому столбцу функцию преобразования данных в строку
Cstr()

@Badya1991 · 20.01.2017, 04:20 **[ТС]**

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

@snipe · 20.01.2017, 04:23

Наверное так

SQL

1
2
3

INSERT INTO БДР ( Организация, Подразделение, Субконто2, Контрагент, СчетДт, СчетКт )
SELECT a1.Организация, a1.Подразделение, a1.Субконто2, a1.Контрагент, CStr(a1.СчетДт) AS СчетДТ1, a1.СчетКт
FROM [Excel 12.0 XML;HDR=YES;IMEX=1;DATABASE=C:\Users\...\БДР.xlsx].[TDSheet$] AS a1;

@Badya1991 · 20.01.2017, 04:24 **[ТС]**

SQL

1
2
3

INSERT INTO БДР ( Организация, Подразделение, Субконто2, Контрагент, СчетДт, СчетКт )
SELECT [TDSheet$].Организация, [TDSheet$].Подразделение, [TDSheet$].Субконто2, [TDSheet$].Контрагент, Cstr([TDSheet$].СчетДт) AS СчетДТ1, [TDSheet$].СчетКт
FROM [Excel 12.0 XML;HDR=YES;IMEX=1;DATABASE=C:\Users\...\БДР.xlsx].[TDSheet$] AS [TDSheet$];

Написал такой запрос пишет ошибка преобразования данных 97 значениям присвоено значение null

ЗЫ: Уже делал так.

@snipe · 20.01.2017, 04:31

столбец то называть надо

Добавлено через 1 минуту
а почему именно 97 значений обNULLилось

Добавлено через 2 минуты
попробуйте для начала просто импортировать таблицу excel
прелесть этого метода в том что при импорте не правильные значения в новую таблицу не вносятся и данные о неправильных значениях попадают в отдельную таблицу (точнее координаты этих данных)

Добавлено через 37 секунд
можно будет просто посмотреть в excel чего с этими данными не так

@Badya1991 · 20.01.2017, 04:36 **[ТС]**

Я выше писал, что первые значения число 20 (20 счет в бухгалтерии), а то значение которое не попало : 90.01.1, он не может распознать как число, и таких значений 97 штук.

Если я в самом первом значении ставлю 20. (с точкой) тогда нет проблем с загрузкой.

@snipe · 20.01.2017, 04:49

а можно файлик excel увидеть

Добавлено через 9 минут
Ну или напишите макрос и запустите его в excel
только запускайте в копии файла и выставьте свои значения констант

Visual Basic

Sub macro1()
dim i#, j#, k#, m#
' i - строка с которой начинается таблица заголовки столбцов не брать, судя по всему у вас это вторая строка
' j - номер столбца по которому двигаться a-1 b-2 c-3 d-4 и т.д предположу что наш корявый столбец 5
' k - последняя строка таблицы
' m- переменная цикла
i=2: j=5: k=100
for m=i to k
Cells(m,j)=CStr(Cells(m,j))
next m
msgbox "Готово"
end sub

Добавлено через 2 минуты
перед запуском макроса выделите столбец полностью и установите в нем формат ячеек Текстовый

@Badya1991 · 20.01.2017, 04:50 **[ТС]**

Desktop.rar Вот база и пример excel, только в запросе отредактируйте свой путь.

@snipe · 20.01.2017, 04:51

к сожалению у меня на работе нет акса
а файлик excel сейчас гляну

@Badya1991 · 20.01.2017, 04:52 **[ТС]**

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

а можно файлик excel увидеть
Добавлено через 9 минут
Ну или напишите макрос и запустите его в excel
только запускайте в копии файла и выставьте свои значения констант

PureBasic

Sub macro1()
dim i#, j#, k#, m#
' i - строка с которой начинается таблица заголовки столбцов не брать, судя по всему у вас это вторая строка
' j - номер столбца по которому двигаться a-1 b-2 c-3 d-4 и т.д предположу что наш корявый столбец 5
' k - последняя строка таблицы
' m- переменная цикла
i=2: j=5: k=100
for m=i to k
Cells(m,j)=CStr(Cells(m,j))
next m
msgbox "Готово"
end sub

Добавлено через 2 минуты
перед запуском макроса выделите столбец полностью и установите в нем формат ячеек Текстовый

Это нужно будет заходить в excel ждать пока строки переформатируются, а хотелось бы на этапе запроса, я так понимаю что это сделать можно, если через мастер импорта добавлять новую таблицу там без проблем

@snipe · 20.01.2017, 04:55

ну а если просто тупо - выделить столбец и выбрать формат ячеек Текстовый
не пролазит

@Badya1991 · 20.01.2017, 05:00 **[ТС]**

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

@alvk · 20.01.2017, 05:57

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

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

должен вас огорчить, вас обманули

в экселе выделяете столбец и меняете формат сразу всего столбца.

Добавлено через 1 минуту

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

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

у меня без всяких махинаций меняет сразу число на текст

@snipe · 20.01.2017, 06:11

alvk,
ох не знаю
дома у меня офис 10 так там проблем нету и на работе офис 2016 - тоже нету
а вот раньшее в 2003 - так там как мне помнится (или уже кажется) такая проблема существовала

@alvk · 20.01.2017, 06:23

Было сложно поменять текст на число, а не наоборот. Тогда нужно было умножать специальной вставкой на 1. Но тут на оборот же!

@Badya1991 · 20.01.2017, 06:44 **[ТС]**

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

должен вас огорчить, вас обманули в экселе выделяете столбец и меняете формат сразу всего столбца.

Я не огорчен, потому что как вы написали так не получится, нужно чтобы в Excel было число как текст, и чтобы это получилось нужно сначала поменять формат, зайти в редактирование ячейки и нажать enter, в итоге появится такой треугольник в углу зеленый который покажет что число как текст, после этого в access загрузится, по вашему методу будет таже ошибка.
Если есть сомнения, скачайте архив поменяйте путь к файлу пример в запросе, сделайте как вы предлагаете и посмотрите что получится.

@alvk · 20.01.2017, 07:30

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

Если есть сомнения, скачайте архив поменяйте путь к файлу пример в запросе, сделайте как вы предлагаете и посмотрите что получится.

У меня есть сомнения, что вы прочли мою подпись, я её не для красоты сделал.

Файл экселя открыл, столбец поменял на текст, без проблем, никаких энтеров не нажимал.

@Badya1991 · 20.01.2017, 07:44 **[ТС]**

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

Файл экселя открыл, столбец поменял на текст, без проблем, никаких энтеров не нажимал.

Вы теперь загрузите в access через запрос эти данные, и увидите что это не текст.

Тема актуальна, решения не нашел, желательно бы без махинаций в excel

mobile · 20.01.2017, 08:08

Badya1991, измените параметр HDR на HDR=No. Первая строка будет именем, текстом и запрос пройдет

SQL

1
2
3

INSERT INTO Таблица1 ( a1 )
SELECT f1 
FROM [Excel 12.0 XML;HDR=NO;IMEX=1;DATABASE=C:\путь\Пример.xlsx].[Лист1$];

Потом удаляете строку с именем поля и все идет без ошибок.

Кстати, параметр XML в драйвере необязательный, он нужен для файлов с программируемой структурой, типа xlsm. Но можно и оставить

Добавлено через 8 минут
Да даже в самом запросе можно не брать строку с именем

SQL

INSERT INTO Таблица1 ( a1 )
SELECT f1 
FROM [Excel 12.0 XML;HDR=NO;IMEX=1;DATABASE=C:\путь\Пример.xlsx].[Лист1$]
WHERE f1<>"a1"

@Badya1991 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 14
	20.01.2017, 05:00 [ТС]	13
	Нет, чтобы в Excel переделать формат из числа в текст вручную надо выделить столбец и потом выделять каждую ячейку и жать Enter 0

@alvk 7433 / 4568 / 299 Регистрация: 12.08.2011 Сообщений: 14,191
	20.01.2017, 05:57	14
	Сообщение от Badya1991 чтобы поменять число в текст нужно выделить ячейку выбрать формат текст потом выбрать ячейку и нажать enter должен вас огорчить, вас обманули в экселе выделяете столбец и меняете формат сразу всего столбца. Добавлено через 1 минуту Сообщение от Badya1991 Нет, чтобы в Excel переделать формат из числа в текст вручную надо выделить столбец и потом выделять каждую ячейку и жать Enter у меня без всяких махинаций меняет сразу число на текст 0

@snipe 4076 / 1456 / 401 Регистрация: 07.08.2013 Сообщений: 3,629
	20.01.2017, 06:11	15
	alvk, ох не знаю дома у меня офис 10 так там проблем нету и на работе офис 2016 - тоже нету а вот раньшее в 2003 - так там как мне помнится (или уже кажется) такая проблема существовала 0

@alvk 7433 / 4568 / 299 Регистрация: 12.08.2011 Сообщений: 14,191
	20.01.2017, 07:30	18
	Сообщение от Badya1991 Если есть сомнения, скачайте архив поменяйте путь к файлу пример в запросе, сделайте как вы предлагаете и посмотрите что получится. У меня есть сомнения, что вы прочли мою подпись, я её не для красоты сделал. Файл экселя открыл, столбец поменял на текст, без проблем, никаких энтеров не нажимал. 0

@snipe 4076 / 1456 / 401 Регистрация: 07.08.2013 Сообщений: 3,629
	20.01.2017, 04:12	2
	надо в excel этот столбец сделать текстовым до загрузки Добавлено через 59 секунд или же в запросе воткнуть по этому столбцу функцию преобразования данных в строку Cstr() 0

@Badya1991 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 14
	20.01.2017, 04:20 [ТС]	3
	Выгружаю я реестр доходов и расходов, выгружается несколько десятков тысяч строк, чтобы поменять число в текст нужно выделить ячейку выбрать формат текст потом выбрать ячейку и нажать enter, если писать макрос я так подозреваю будет долгое преобразование, нельзя на этапе запроса преобразовать число в текст, как то же через мастер импорта число в текст преобразуется и без проблем, если изначально выставить его в текст. 0

@snipe 4076 / 1456 / 401 Регистрация: 07.08.2013 Сообщений: 3,629
	20.01.2017, 04:31	6
	столбец то называть надо Добавлено через 1 минуту а почему именно 97 значений обNULLилось Добавлено через 2 минуты попробуйте для начала просто импортировать таблицу excel прелесть этого метода в том что при импорте не правильные значения в новую таблицу не вносятся и данные о неправильных значениях попадают в отдельную таблицу (точнее координаты этих данных) Добавлено через 37 секунд можно будет просто посмотреть в excel чего с этими данными не так 0

@Badya1991 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 14
	20.01.2017, 04:36 [ТС]	7
	Я выше писал, что первые значения число 20 (20 счет в бухгалтерии), а то значение которое не попало : 90.01.1, он не может распознать как число, и таких значений 97 штук. Если я в самом первом значении ставлю 20. (с точкой) тогда нет проблем с загрузкой. 0

@Badya1991 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 14
	20.01.2017, 04:50 [ТС]	9
	Desktop.rar Вот база и пример excel, только в запросе отредактируйте свой путь. 0

@snipe 4076 / 1456 / 401 Регистрация: 07.08.2013 Сообщений: 3,629
	20.01.2017, 04:51	10
	к сожалению у меня на работе нет акса а файлик excel сейчас гляну 0

@snipe 4076 / 1456 / 401 Регистрация: 07.08.2013 Сообщений: 3,629
	20.01.2017, 04:55	12
	ну а если просто тупо - выделить столбец и выбрать формат ячеек Текстовый не пролазит 0

@alvk 7433 / 4568 / 299 Регистрация: 12.08.2011 Сообщений: 14,191
	20.01.2017, 06:23	16
	Было сложно поменять текст на число, а не наоборот. Тогда нужно было умножать специальной вставкой на 1. Но тут на оборот же! 0

@Badya1991 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 14
	20.01.2017, 06:44 [ТС]	17
	Сообщение от alvk должен вас огорчить, вас обманули в экселе выделяете столбец и меняете формат сразу всего столбца. Я не огорчен, потому что как вы написали так не получится, нужно чтобы в Excel было число как текст, и чтобы это получилось нужно сначала поменять формат, зайти в редактирование ячейки и нажать enter, в итоге появится такой треугольник в углу зеленый который покажет что число как текст, после этого в access загрузится, по вашему методу будет таже ошибка. Если есть сомнения, скачайте архив поменяйте путь к файлу пример в запросе, сделайте как вы предлагаете и посмотрите что получится. 0

@Badya1991 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 14
	20.01.2017, 07:44 [ТС]	19
	Сообщение от alvk Файл экселя открыл, столбец поменял на текст, без проблем, никаких энтеров не нажимал. Вы теперь загрузите в access через запрос эти данные, и увидите что это не текст. Тема актуальна, решения не нашел, желательно бы без махинаций в excel 0

При импорте в Access из Excel Ошибка типа данных (#Число!)

Решение