Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Импорт из CSV в XLS
 
#1
26.05.2014 21:39:14
Приветствую,
Необходимо сделать импорт из csv в excel, с проверкой значений, - самостоятельно осилить не могу.
Что есть:
- 2 файла: price.csv и catalog.xls
- файл price.csv содержит 2 колонки: Артикул и Цена
- файл catalog.xls содержит 8 колонок, в т.ч. Артикул и Цена
Примечание: порядковый номер колонок Артикул и Цена, в каждом файле отличаются.

Что надо:
- обновить цену по каждому артикулу, - т.е. должна быть проверка на равенство:
1. берем значение ячейки A2 из файла price.csv и ищем такой же в колонке D в файле catalog.xls
2. если нашли совпадение, то: берем значение из ячейки B2 файла price.csv и добавляем в ячейку G2 в файле catalog.xls
Примечание:
- последовательность (номер строки) Артикула может не соответствовать.
- колонки могут менять порядковый номер, поэтому хотелось бы обращаться к колонке не по номеру а по имени (чую это про Диапазоны что-то)

Прошу помочь, направить в нужную сторону, если не сложно показать пример
Исходные файлы прикрепил
Заранее, благодарю
 
 
 
#2
26.05.2014 21:47:47
Можете попробовать такое решение:
http://excelvba.ru/programmes/Lookup

номера сравниваемых столбцов, и что откуда и куда подставлять, - всё задаётся в настройках программы
Надстройки для Excel  Парсинг сайтов  Мониторинг цен конкурентов
 
 
 
#3
26.05.2014 21:56:55
Судя по описанию - отличный макрос, но
1. хотелось бы реализовать задачу стандартными методами excel;
2. 35 евроденег для НЕкомерческого использования, - дороговато как по мне  :)  

За наводку спасибо, узрел фцнкцию =ВПР() - буду продвигатся в этом направлении.
Вопрос открыт, советы принимаются... )
 
 
 
#4
26.05.2014 22:27:56
Хорошо, есть решение подешевле: http://ibay.narod.ru/other/CompareFiles.Find.rar
Делает примерно то же что и код тёзки.
Только т.к. эксель по умолчанию открывает csv как придётся, а код писался под родные файлы - в данном случае у меня в русском 2007 необходимо csv открыть как положено (через мастер импорта данных или через меню открытия файла или даблкиком правильно открывается, а кодом нет - делит по запятой), затем сохранить как xls/x.
Но если кодом открывает правильно - то можно не пересохранять.
Далее прописываем в настройках все пути, жмём кнопку:

Файл - приёмник: c:\Downloads\Max Human\catalog.xlsx
Файл - источник: c:\Downloads\Max Human\price.xlsx
Столбцы сравнения в приёмнике: D
Столбцы сравнения в источнике: A
Лист - приёмник (№): 1
Лист - источник (№): 1
Столбцы - приёмники данных копирования: G
Столбцы - источники данных копирования: B

Можно дополнительно прописать столбец для пометок:
Столбец для пометок в приёмнике: H
Можно конечно всё это делать и с помощью ВПР() - но если цены будете обновлять частично, то нужно будет придумывать ещё другую формулу, затем возиться с спецкопипастом...
 
 
 
#5
26.05.2014 22:49:50
Цены заменять новыми "навовсе"?
Или добавлять в "правый хвост" столб с новыми ценами?
 
 
 
#6
26.05.2014 23:40:54
Заменять на новые, по сути - обновлять цены
 
 
 
#7
27.05.2014 01:45:52
Файл Price.csv сохранен, как Price.xls.
 
 
 
#8
27.05.2014 02:07:24
Приложил пример использования ВПР. Распакуйте оба файла в одну и ту же папку.
Загрузите в Excel книгу catalog.xlsx и посмотрите формулу в ячейках столбца G.
Данные обновляются с ленты: Данные - Обновить все или по Ctrl-Alt-F5
Про ВПР подробно есть здесь в приемах Николая Павлова: Использование функции ВПР (VLOOKUP) для подстановки значений
 
 
 
#9
27.05.2014 02:52:56
Цитата
ZVI пишет: и посмотрите формулу в ячейках столбца G.
нет ее там   :oops:  
Супер! Большое спасибо. Заодно поковыряю макрос, для общего развития :)

Вопрос: такой импорт, на объеме 7-10 тысяч строк будет сильно грузить пк?
 
 
 
#10
27.05.2014 03:10:03
Формула введена в G2 и протянута ниже.
Для заполнения формулы в ячейки ниже можно стать на G2 и кликнуть мышкой на правый нижний угол (там крестик).
Для 10 тысяч будет работать быстро.
Обратите внимание, что в формуле указан диапазон $A$1:$B$1000 файла [price.csv], откорректируйте номер последней ячейки с некоторым запасом, если это нужно.
В примере используется способность функции ВПР работать с закрытой книгой, с CSV в данном случае.
 
 
 
#11
27.05.2014 09:58:47
У меня этот csv что дома, что на работе открывается по умолчанию с разделением по запятым - т.е. ВПР() по csv не работает.
Как и мой макрос...
 
 
 
#12
27.05.2014 12:01:53
Цитата
Hugo пишет: открывается по умолчанию с разделением по запятым
Игорь, да, в данном CSV в качестве разделителя целой и дробной части используется русский вариант - запятая, и точка с запятой в качестве разделителя столбцов ( параметр настройки операционки "Язык и региональные стандарты" ) .
 
 
 
#13
27.05.2014 12:28:47
В том и дело - я без изменения настроек системы не могу корректно на работе обработать такой файл.
Вариант один - сперва открыть этот csv через импорт данных, затем уже ставить ВПР().
Или пересохранить как xls - а там уже работаеют все другие варианты.
Ещё есть вариант писать спецмакрос - вероятно это сделала Мотя :)
 
 
 
#14
27.05.2014 12:29:36
Владимир, в Вашем примере происходит динамическая синхронизация - меняю значение в csv - автоматом обновляется в xls. Можно как то перевести на ручное обновление, через Данные - Подключения - Обновить все? Или иным способом?

И еще момент, если внести изменения в csv и закрыть, потом открыть xls - то значение не обновляется (даже если обновит связи с диалогового окна или на вкладке Данные). Обновление происходит только если открыть csv и xls вместе.

Заранее, благодарю
Изменено: Max Human - 27.05.2014 16:08:07
 
 
 
#15
27.05.2014 12:53:13
Если уж открываете в экселе этот csv - то сохраните его в любом нормальном формате экселя. С csv у него всегда проблемы - надёжно только спецмакросом можно обрабатывать.
 
 
 
#16
27.05.2014 13:07:47
Цитата
Мотя пишет: Файл Price.csv сохранен, как Price.xls.
А можно как то обойтись без дополнительной вкладки csv в файле catalog?
И насколько я понял, кнопка нужна для "указания пути к файлу" ? Если так, то можно вместо кновпки прописать путь прямо в макросе?

Заранее, благодарю  :)
 
 
 
#17
27.05.2014 14:10:21
Цитата
Max Human    А можно как то обойтись без дополнительной вкладки csv в файле catalog?
Разумеется (см. файл). Лист был предусмотрен для быстрого визуального контроля.
Цитата
Max Human    И насколько я понял, кнопка нужна для "указания пути к файлу" ?
Вообще-то, макросная кнопка и в Африке является макросной кнопкой.
Без нее запросто можно обойтись.
"Путь к файлу" запрашивает макрос, а не кнопка..
Цитата
Max Human    Можно прописать путь прямо в макросе?
Разумеется.
В макросе заменить:
Put_File_csv = Application _
   .GetOpenFilename("EXCEL Files (*.XLS, *.XLS", , "УКАЖИТЕ file XLS-PRICE")
на
Put_File_csv = "Ваш конкретный путь к файлу"
Однако, при изменении местоположения прайс-файла (*.xls), следует не забывать корректировать в макросе "путь к файлу".
Как вариант...
Можно, указывать (жестко) в ячейке в файле Catalog этот путь, а макрос заставить его записывать из этой ячейки в Put_File_csv.
 
 
 
#18
27.05.2014 16:10:48
Ещё вариант -
Код
Option Explicit

Sub Price()
    Dim fd As FileDialog, a, b, sep$, i&, t
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Filters.Add "CSV files", "*.csv", 1
    End With
    If fd.Show = -1 Then
        a = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fd.SelectedItems(1), 1).ReadAll, vbCrLf)
        With CreateObject("Scripting.Dictionary"): .comparemode = 1
            sep = Mid(1 / 2, 2, 1)
            For i = 1 To UBound(a)
                If InStr(a(i), ";") Then
                    t = Split(a(i), ";")
                    .Item(t(0)) = Replace(t(1), ",", sep)
                End If
            Next
            a = Sheets(1).UsedRange.Columns(4).Value
            b = Sheets(1).UsedRange.Columns(7).Value
            For i = 1 To UBound(a)
                If .exists(a(i, 1)) Then b(i, 1) = .Item(a(i, 1))
            Next
            Sheets(1).UsedRange.Columns(7).Value = b
        End With
    End If

    Set fd = Nothing
End Sub
 
 
 
#19
27.05.2014 17:04:39
Цитата
Max Human пишет:
Можно как то перевести на ручное обновление, через Данные - Подключения - Обновить все?...
И еще момент, если внести изменения в csv и закрыть, потом открыть xls - то значение не обновляется
Приложил такой вариант без макросов. Открытие CSV в excel не требуется.
В книге есть скрытый лист Price с запросом данных из CSV.
Запрос настроен на обновление только при открытии книги и вручную через  Данные - Подключения - Обновить все
Отображается скрытая книга правым кликом внизу на названии листа и выбором "Отобразить".
Скрывается там же
Изменено: ZVI - 27.05.2014 17:10:55
 
 
 
#20
27.05.2014 17:36:01
Обратите внимание - мой вариант не затирает существующие цены, по которым нет обновления (кажется единственный из представленных).
Если нужно - можно прикрутить "сигнальный" столбец, чтоб было видно что обновилось.
 
 
 
#21
27.05.2014 18:34:46
Цитата
Hugo Обратите внимание - мой вариант не затирает существующие цены, по которым нет обновления (кажется единственный из представленных).
У меня - аналогично. :D
 
 
 
#22
27.05.2014 18:46:35
Мотя, извиняюсь - Ваш файл не смотрел.
Но моё решение будет побыстрее на большом количестве данных. И работает с родным csv.
 
 
 
#23
27.05.2014 19:02:45
Цитата
Hugo Но моё решение будет побыстрее на большом количестве данных.
Бесспорно - побыстрее на большом количестве данных.
Но для сермяжного Прайса - это не актуально.  :D  
Цитата
Hugo ... с родным csv...
Это, разумеется, критично.
Цитата
Hugo Мотя, извиняюсь - Ваш файл не смотрел.
Куда уж нам с нашим ... в калашный ряд!  :D
Изменено: Мотя - 27.05.2014 19:07:12
 
 
 
#24
28.05.2014 22:54:09
Hugo, Мотя, ZVI, спасибо за помощь :)

Сейчас разбираюсь с Вашими примерами, - очень помогает!
 
 
 
Страницы: 1
Читают тему
Наверх