Форум программистов, компьютерный форум, киберфорум
Наши страницы
MS Access
Войти
Регистрация
Восстановить пароль
 
 
Рейтинг 4.80/5: Рейтинг темы: голосов - 5, средняя оценка - 4.80
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
1

Перенос данных из Excel с использованием диалога выбора файла

06.11.2017, 10:39. Просмотров 968. Ответов 20
Метки нет (Все метки)

Всем здравствуйте!
Подскажите пожалуйста по коду следующее.
Есть таблица Access "Заявки", данные в которую подгружаются через шаблонный файл Excel используя связанные источники данных, при этом приходится вставлять данные из исходного файла екселя в шаблонный файл екселя, связанный с БД и затем только уже переносить в Access, решил немного модернизировать это действо, убрать это "лишнее" звено, тем более что структура файлов екселя - шаблона и источника абсолютно одинаковая. Данный существующий вариант переноса применяется потому, что каждый день плановый -диспетчерский отдел скидывает заявки в производство в виде графика производства (он же файл источник) наименованный в виде сегодняшней даты, например "6112017" и такая дребедень каждый день.
В связи с чем возникла мысль, реализовать это дело через прямой переброс данных из новой таблицы екселя в ацесс методом выбора нужного файла и загрузки его содержимого в ацесс минуя шаблон.
Диалог открытия файла на кнопку навесил,

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
Dim nFile DAO.Recordset
        With Application.FileDialog(3) 'msoFileDialogFilePicker
            .Title = "Выбрать график производства 1С для загрузки в работу "
            .InitialFileName = CurrentProject.Path
            .Filters.Add "Excel files", "*.xls*", 1
            .InitialFileName = "L:\Цех\Заявки ПДО"
            .AllowMultiSelect = False
            If .Show And .SelectedItems.Count > 0 Then
         Else
                MsgBox "Файл не выбран"
                Exit Sub
            End If
        End With
далее нужно реализовать загрузку данных из этого графика в таблицу.Как в моем случае лучше и проще это сделать?


Файл источник екселя имеет столбцы: [ДатаЗаявки][Изделие][Деталь][Количество][ДатаГотовности]
Табица access имеет точно такие же наименования столбцов.
Подскажите пожалуйста как описать далее в коде процедуру перемещения данных из екселя в ацесс, чтобы вновь переносимые данные добавлялись к уже имеющимся в таблице.
Спасибо.
0
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
06.11.2017, 10:39
Ответы с готовыми решениями:

Средства диалога выбора файла
Есть ли какие-то минимальные средства для реализации диалога выбора файла на С (в консольной...

Получить имя файла из диалога выбора файла
Нашел класс диалога выбора файла: package com.stetsenko.openfiledialog; import...

Не получается избавиться от Qt-шного диалога выбора файла
QString mOpenFilter = "All supported (*.bmp *.gif *.ico *.jpeg *.jpg *.mng *.pbm *.pgm *.png *.ppm...

Начало загрузки по закрытию диалога выбора файла
Сабж. Сейчас делаю во так: <asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button...

ошибка при вызове диалога выбора файла
Всем доброго времени суток. Столкнулся с такой ошибкой: в программе using System; using...

20
Kkarn
883 / 274 / 49
Регистрация: 02.12.2014
Сообщений: 1,199
06.11.2017, 12:57 2
По идее, от диалога выбора файла вам нужен полный путь к файлу.
Затем нужно создать запрос к данному файлу, что-то вроде вот такого:

CurrentDb.Execute "SELECT * INTO ИмяВременнойТаблицыВБазе FROM [" & ИмяЛистаВФайле & "] IN '" & ПолныйПутьКФайлу & "'" & ВерсияФайлаЭксель & "; HDR=Yes;IMEX=1;]"

При этом "ВерсияФайлаЭксель" может быть либо "[Excel 8.0" - это для xls, либо "[Excel 12.0 XML" - это для xlsx.

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

Если ничего не напутал, то как-то так).
1
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 06:34  [ТС] 3
Немного перестарался с корректировкой кода
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Private Sub Кнопка1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim nFile As DAO.Recordset
        With Application.FileDialog(3) 'msoFileDialogFilePicker
            .Title = "Выбрать график производства 1С для загрузки в работу "
            .InitialFileName = CurrentProject.Path
            .Filters.Add "Excel files", "*.xls*", 1
            .InitialFileName ="L:\Цех\Заявки ПДО"
            .AllowMultiSelect = False
            If .Show And .SelectedItems.Count > 0 Then
                nFile = .SelectedItems(1) 'путь к файлу и его наименование
 
            Else
                MsgBox "Файл не выбран"
                Exit Sub
            End If
        End With
Попробовал создать запрос, чего то не получилось, не совсем корректно ввел данные или что то не так прописал.
выкладываю файл источник,может так проще будет понять чем на словах
0
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 07:54  [ТС] 4
Само собой, можно перекроить запрос, чтобы он вставлял данные непосредственно в нужную таблицу
Это был бы самый лучший вариант

Добавлено через 1 час 15 минут
Сделал импорт через TransferSpreadsheet

Visual Basic
1
2
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "ЗаявкиПДО", nFile, False
Но он почему то сливает все вместе с заголовком, хотя я задал False....
Подскажите как ему указать диапазон ячеек, которые нужно импортировать?
/Форматировать данные все равно придется через промежуточную таблицу похоже
0
07.11.2017, 07:54
mobile
Эксперт MS Access
23746 / 13301 / 2807
Регистрация: 28.04.2012
Сообщений: 14,564
07.11.2017, 08:35 5
Цитата Сообщение от Braser Посмотреть сообщение
придется через промежуточную таблицу похоже
Наверняка. С екселем, как правило, иначе не получается. А процедуру можно немного поправить, чтобы лишний раз не искать файл
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Private Sub Кнопка1_Click()
    Dim nFile, path, file
    path="L:\Цех\Заявки ПДО\"   'путь
    file=path & Format(Date, "yyyymmdd") & ".xls" 'Имя файла с путем и сегодняшней датой, как в выложенном файле
    If dir(file) <> "" Then  'Если такой файл есть, импорт. Иначе поиск файла
        'Импорт во временную таблицу Temp
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Temp", file, False 
    Else
        With Application.FileDialog(3) 'msoFileDialogFilePicker
            .Title = "Выбрать график производства 1С для загрузки в работу "
            .InitialFileName = CurrentProject.Path
            .Filters.Add "Excel files", "*.xls*", 1
            .InitialFileName =path
            .AllowMultiSelect = False
            If .Show And .SelectedItems.Count > 0 Then
                nFile = .SelectedItems(1) 'путь к файлу и его наименование
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Temp", nFile, False
            Else
                MsgBox "Файл не выбран"
                Exit Sub
            End If
        End With
0
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 08:58  [ТС] 6
mobile,Спасибо ОГРОМЕНСКОЕ, как всегда выручили... я правда сам уже почти додумал, но маленько по другому...но это не особо важно. Подскажите пожалуйста, как при выходе из формы очистить таблицу "Temp" от записей? Удалять ее совсем мне не нужно, потому что у меня на ее основе имеется форматированный под мои нужды запрос, а вот почистить от записей после перемещения надо бы обязательно...
0
mobile
Эксперт MS Access
23746 / 13301 / 2807
Регистрация: 28.04.2012
Сообщений: 14,564
07.11.2017, 09:25 7
Запрос в коде на удаление записей из Temp.
Visual Basic
1
currentdb.execute "delete * from Temp"
0
Kkarn
883 / 274 / 49
Регистрация: 02.12.2014
Сообщений: 1,199
07.11.2017, 09:47 8
Почистить таблицу можно выполнением простого запроса на удаление записей из этой таблицы. То есть:

CurrentDb.Execute "DELETE * FROM Temp"

Меня вот только смущает такой момент: не перенакрывается ли таблица Temp при выполнении DoCmd.TransferSpreadsheet? Не помню точно этот нюанс. Ведь если в ходе выполнения такого импорта данные каждый раз пишутся во вновь создаваемую таблицу Temp, то хоть чисти ее, хоть не чисти - она каждый раз при импорте будет удаляться и заменяться новой... Вы проверьте этот момент.

Добавлено через 8 минут
Цитата Сообщение от Braser Посмотреть сообщение
Но он почему то сливает все вместе с заголовком, хотя я задал False....
Ну, в принципе, и не удивительно, что сливает. Ведь у вас в файле сначала идут несколько "лишних" строк: пустые строки, строка с названием таблицы, и только потом - строка заголовков полей. Само собой, что прога путается. Если бы в вашем файле сразу в первой строке шли заголовки полей - другое дело...

Добавлено через 9 минут
Вообще в случае с вашей таблицей, я бы втягивал ее в базу Access "как есть", то есть без указания заголовков и т.д. В таком случае она втянется со стандартными именами полей типа "F1", "F2" и т.д. И потом строил запрос на добавление строк в основную таблицу, исходя из имеющихся стандартных имен полей, отфильтровав при этом ненужные строки (со словами "График выпуска продукции..." и т.д.) - их можно отбросить, например, отфильтровав первый столбец по значениям даты.

Добавлено через 1 минуту
И вообще было бы замечательно, если бы вы бросили сюда кусочек базы со всеми этими кнопками и т.д. - было бы гораздо оперативнее проверять варианты и думать над вопросами).
0
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 10:23  [ТС] 9
не перенакрывается ли таблица
с этим все нормально. Мне главное чтобы она не удалялась вообще, дабы запрос отформатированный не потерялся. Другой проблем возник. При переносе и сохранении данных в табличку TEMP выскакивает ошибка сохранения при автозамене имен и создается табличка которая эти ошибки содержит. что интересно там в ней ничего нет практически, записи вроде не теряются....почему она создается и ошибка эта выскакивает? Может потому что в таблице TEMP некоторым полям присвоил значения даты и числовые?
0
Kkarn
883 / 274 / 49
Регистрация: 02.12.2014
Сообщений: 1,199
07.11.2017, 10:28 10
Почему-то мне кажется, что вот это:
Цитата Сообщение от Braser Посмотреть сообщение
выскакивает ошибка сохранения при автозамене имен и создается табличка которая эти ошибки содержит
как раз и говорит о том, что таблица Temp как раз таки и "удаляется вообще"... Хотя дальше вы пишете, что "записи вроде не теряются"...
В общем, мое мнение я написал выше: сначала перебрасывайте данные из Экселя во временную таблицу Temp, потом из нее - уже в какую-то постоянную таблицу, в которой как раз таки и присваивайте полям нужные типы данных. То есть чтобы в самой таблице Temp были чисто "сырые" данные, без всякой привязки к типам данных, поскольку скорее всего в ходе импорта эта таблица полностью заменяется.
0
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 10:30  [ТС] 11
Цитата Сообщение от Kkarn Посмотреть сообщение
в случае с вашей таблицей
так и делаю, все прекрасно вроде проходит, данные не теряются, но чего-то не совсем хорошо,ошибка эта выскакивает.

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

Добавлено через 1 минуту
Цитата Сообщение от Kkarn Посмотреть сообщение
уже в какую-то постоянную таблицу,
Вооот, опять двойная фильтрация или форматирование, от которых пытаюсь уйти....
0
Kkarn
883 / 274 / 49
Регистрация: 02.12.2014
Сообщений: 1,199
07.11.2017, 10:36 12
Все-таки постарайтесь как-то вырезать в отдельную маленькую базу всё то, что участвует в нашем обсуждаемом процессе, и бросьте сюда, чтобы не гадать на пальцах).
Кстати, я вычитал в хелпе по методу DoCmd.TransferSpreadsheet, что там есть возможность указать импортируемый диапазон ячеек. Только вот не знаю, чем это вам поможет, т.к. вряд ли ведь вы при импорте будете заранее знать конечные значения заполненных ячеек...
0
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 10:45  [ТС] 13
Только вот не знаю, чем это вам поможет, т.к. вряд ли ведь вы при импорте будете заранее знать конечные значения заполненных ячеек...
так я ж об это и хочу сказать, что если указать диапазон, можно убрать заголовок из импорта, а импортировать все начиная с 9 строки и до 300 например, такого диапазона точно хватит.Почему с 9-й, потому что данные, которые нужны с нее начинаются и ниже идут
0
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 10:50  [ТС] 14
Вот собственно
0
Kkarn
883 / 274 / 49
Регистрация: 02.12.2014
Сообщений: 1,199
07.11.2017, 10:51 15
Тогда попробуйте добавить в строку после слова False еще и диапазон, что-то типа такого:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Temp", nFile, False, "A9:G300"

Букву G я от фонаря поставил, поставьте нужную.

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

Давайте мысленный эксперимент, чтобы я уяснил для себя, что происходит с таблицей Temp:
Вот если вы сделали импорт 1 раз, у вас втянулись какие-то строки в таблицу Temp, так?
Теперь вы СРАЗУ ЖЕ делаете импорт ВТОРОЙ РАЗ. При этом строки нового импорта ДОБАВЛЯЮТСЯ в таблицу Темп? Старые строки (которые с первого раза были) - остаются?
0
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 11:08  [ТС] 16
Запрос правда работать не будет без ресурсов

Добавлено через 2 минуты
если таблица удаляется при импорте, то я так понимаю должны удаляться установленные для нее типы полей, но этого почему-то не происходит... Там постоянно пишется про 4 записи удаляемые при импорте, но вроде как все записи которые необходимо записать в ТЕМП остаются

Добавлено через 13 минут
Да, старые остаются, так что таблица не создается еще раз, используется старая

Добавлено через 1 минуту
Пробовал указать диапазон, что то не особо помогло, вообще ничего не цепляет
1
Kkarn
883 / 274 / 49
Регистрация: 02.12.2014
Сообщений: 1,199
07.11.2017, 11:17 17
Посмотрел.
Действительно, таблица Temp при импорте не удаляется, это хорошо). В нее просто добавляются строки из экселевского файла.
Да, увидел сообщение про 4 записи, которые удаляются. Однозначно это сообщение связано с тем, что вы установили типы данных, которые не подходят для импортируемых данных. То есть, например, вы задали для поля F1 тип "дата", а в нем есть еще строки со словами "График выпуска продукции", "Дата" и т.д.
В принципе, мы ничего нужного при этом не теряем, удаляются как раз те строки, которые не нужны, но сообщение так и будет выскакивать, если не убрать ограничения на типы данных. Либо, возможно, его как-то можно подавить в коде, скорее всего. Только вот как - навскидку не скажу, не помню. Но вообще-то это неправильно - подавлять такие сообщения, нужно изначально правильно импортировать данные...

Что же касается таблицы автозамены имен, то я изначально ее удалил - и больше она у меня не появлялась)). Не знаю, из-за чего она появилась в первый раз у вас, но в дальнейшем я сделал импорт несколько раз - не появилась.

Добавлено через 5 минут
Короче говоря, добавьте в команду импорта после слова False еще вот это:

, "A9:M300"

и мы избавляемся от сообщений о тех 4 записях, которые не импортируются.

Добавлено через 1 минуту
Цитата Сообщение от Braser Посмотреть сообщение
Пробовал указать диапазон, что то не особо помогло, вообще ничего не цепляет
Значит, что-то неправильно указали. Я написал вот так:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Temp", file, False, "A9:M300"

В обоих местах, где есть эта строка. Замечательно цепляет.
1
mobile
Эксперт MS Access
23746 / 13301 / 2807
Регистрация: 28.04.2012
Сообщений: 14,564
07.11.2017, 11:21 18
Braser, если не хотите удалять временную приемную таблицу, а добавлять в нее записи, то можно воспользоваться запросами. Код примерно такой на кнопке (если имя листа постоянное)
Visual Basic
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
Private Sub Кнопка1_Click()
    Dim nFile, path, file, s, db As DAO.Database, msg
    Set db = CurrentDb
    path = "L:\Цех\Заявки ПДО\" 'путь
    db.Execute "delete * from Temp" 'Удаляем из Temp
    file = path & Format(Date, "yyyymmdd") & ".xls" 'Имя файла с путем и сегодняшней датой, как в выложенном файле
 
    'SQL запроса на импорт данных и вставку в Temp. Импортируемые поля надо уточнять. Взято от фонаря
    s = "insert into Temp ([Дата заявки], Изделие, Деталь,  Количество, [ДатаГотовности]) " _
    & "SELECT F1, F10, F11, F13, F6 " _
    & "FROM (SELECT * " _
    & "FROM [TDSheet$A9:M300] IN '" & file & "'[Excel 8.0; hdr=No;IMEX=1])"
 
    If Dir(file) <> "" Then  'Если такой файл есть, импорт. Иначе поиск файла
        'Импорт во временную таблицу Temp
        db.Execute s
        msg = "Данные импортированы"
    Else
        With Application.FileDialog(3) 'msoFileDialogFilePicker
            .Title = "Выбрать график производства 1С для загрузки в работу "
            .InitialFileName = CurrentProject.path
            .Filters.Add "Excel files", "*.xls*", 1
            .InitialFileName = path
            .AllowMultiSelect = False
            If .Show And .SelectedItems.Count > 0 Then
                nFile = .SelectedItems(1) 'путь к файлу и его наименование
                db.Execute s
                msg = "Данные импортированы"
            Else
                msg = "Файл не выбран"
                Exit Sub
            End If
        End With
    End If
    MsgBox msg
End Sub
1
Kkarn
883 / 274 / 49
Регистрация: 02.12.2014
Сообщений: 1,199
07.11.2017, 11:24 19
Цитата Сообщение от mobile Посмотреть сообщение
FROM [TDSheet$A9:M300] IN '" & file & "'[Excel 8.0; hdr=No;IMEX=1])
Воот)) Возвращаемся к примерно той схеме, которую я предложил в первом своем ответе. То есть к непосредственному запросу данных из листа. Помнится, вы, mobile, меня этому фокусу еще давным давно научили, теперь вот всегда им пользуюсь и другим советую)).
1
Braser
221 / 100 / 22
Регистрация: 16.03.2013
Сообщений: 909
07.11.2017, 11:34  [ТС] 20
Цитата Сообщение от Kkarn Посмотреть сообщение
Короче говоря, добавьте в команду импорта после слова False еще вот это
ТОЧНО пропало! Супергут! я диапазон неправильно указывал.
Спасибо друзья, сегодня я счастлив, сделана еще одна "мулька" в рабочей БД.
Думаю mobile не обидитесь, если автором лучшего ответа будет в этой теме KKarn.
Еще раз СпАсИбО,БОЛЬШОЕ!!!
0
07.11.2017, 11:34
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
07.11.2017, 11:34

Чтение из файла Excel и перенос данных в memo
день добрый! столкнулся с такой проблемой необходимо столбца exel перенести данные в memo поле на...

Перенос данных из одного файла Excel в другой
Добрый вечер! Я новичок в программирование. надеюсь поможете. такая задачка. имеется файл эксель в...

Перенос данных из файла xlsx (Excel) в ListView
Есть файл Excel (xlsx). формата: Пользователь Пароль user 123 user1 ...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
20
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Рейтинг@Mail.ru